Forum Discussion
Calculation Error in Measure
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!
Instead of VALUES('Date'[Date]) <= TODAY() you should use MIN('Date'[Date]) <= TODAY()
4 Replies
- sdjensenSolution Sage
Instead of VALUES('Date'[Date]) <= TODAY() you should use MIN('Date'[Date]) <= TODAY()
- sdjensenSolution Sage
It's because you use the formula in another context which cause VALUES to return more than one date which causes the error.
- Ajaykumar28155New MemberHeadcount =CALCULATE(DISTINCTCOUNT(Headcount[Person Number]),FILTER(Headcount,Headcount[Termination Date]=Headcount[Headcount Month]))+CALCULATE(DISTINCTCOUNT(Headcount[Person Number]),ISBLANK(Headcount[Termination Date]))