Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

TOTALYTD - wrong results

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!

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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]

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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])