TOTALYTD - wrong results

[Anonymous]

Hello community,

I was interested in "how to calculate year-to-date sales more efficient". Until today, I always said something like this: 

MaxDate = 29.09.2020

YearStart = 01.01.2020

Calculate(Sum(Sales), Filter( YearStart<=Date <= MaxDate))

But there must be a more elegant way to do this. I tried the DAX-Functions TOTALYTD & a combination of CALCULATE + DATESYTD like this: 

TOTALYTD

TOTALYTD( CALCULATE (Sum ( 'SALES'[Amount] ) ),'SALES'[Date] ) 

RESULT: Sum up whole year (500 instead of 350 till today)  

CALCULATE + DATESYTD

CALCULATE( Sum ( 'SALES'[Amount] ), DATESYTD ( 'SALES'[Date] ) )

RESULT: Sum up whole years (3000 instead of 350 till today)

What am I missing? Why is Power BI including all values till year-end and doesn't stop at todays date (29.09.2020)?  Thank you in advance!

[PaulDBrown · Accepted solution]

@Anonymous 

To use Time intelligence functions, you need a Date Table as a Dimension table with continuous dates covering the range of dates in your model.

You then use the Dates Table [Date] field in the expressions instead of your Sales [Date]

[Anonymous]

Hi PaulDBrown, 

thanks. Now, it calculcates the running total. But still till the end of year and not ytd.

Is a flag or explicit definition of todays date mandatory? 

     

I'd expect that the calculation ends up here...

[PaulDBrown]

@Anonymous 

Yes, if you want the measure to stop at Today's date you need to specify it (otherwise it will return the values included in the filter context). 
One way of doing this is:

today cutoff =
IF(MAX(Date[Date]<= TODAY(), [Sales Ytd TotalYtd])