Calculation Error in Measure

[DieLem]

Hi,

I have two graphs that look similar but are different.

The first shows a target line of registrations with the current YTD registrations as a column below. It was changed to only show the YTD until today and leave the rest of the year blank using this formula: 

IncrementTr YTD = if(VALUES('Date'[Date])<=TODAY(),CALCULATE([IncrementTr Total],DATESYTD('Date'[Date],"30/8")),BLANK())

This one works fine.

I want to replicate this logic on a second graph. The second has a target revenue line and YTD revenue as the columns.

 

However this gives an error when I apply the same logic to the revenue YTD formula:

Test = if(VALUES('Date'[Date])<=TODAY(),CALCULATE([Amount Total 18A],DATESYTD('Date'[Date],"30/8")),BLANK())

Here the error:

I would like the revenue column to also be blank after any date from today like in the first graph.

A link to the PowerBI file:

https://drive.google.com/open?id=154JP-ZQkBhkGAxt8cEHdgyaj7ZQ1ZKdI (Filename: TrackingExample2)

Thanks in advance!

[Ajaykumar28155]

Headcount =

CALCULATE(

    DISTINCTCOUNT(Headcount[Person Number]),FILTER(Headcount,Headcount[Termination Date]=Headcount[Headcount Month])

)

+

CALCULATE(

    DISTINCTCOUNT(Headcount[Person Number]),ISBLANK(Headcount[Termination Date])

)

[sdjensen · Accepted solution]

Instead of VALUES('Date'[Date]) <= TODAY() you should use MIN('Date'[Date]) <= TODAY()

[DieLem]

Thanks so much @sdjensen !

Can you explain to me why it would work in one graph but on in the other? Want to understand so I can handle it myself in future.

Thanks!

[sdjensen]

It's because you use the formula in another context which cause VALUES to return more than one date which causes the error.