Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

TOTALYTD until TODAY

Dear all, I´m trying a "simple" measure to calculate the cumulative values from the beginning of the year until "today".   The formula below works, but notice I´m fixing the "year_end_date"   YT...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Anonymous

     

    Just clarifying your requirements...

    If today is 5-Sep-2017, then what date range do you expect the formula to calculate over?

    1. 1-Jan-2017 to 5-Sep-2017 (cumulative total as at today, but with years ending on 31-Dec) 
    2. 6-Sep-2016 to 5-Sep-2017 (cumulative total as at today, and with years ending on today's date, which would be the same as summing the last year ending today)
    3. Something else?

    As a side note, in the TOTALYTD or DATESYTD functions, year_end_date can never be any expression apart from a string literal. If you want a flexible year_end_date, you have to write the time intelligence logic from scratch without using the built-in time intelligence functions.

     

    Owen :)

  • Anonymous's avatar
    Anonymous
    9 years ago

    OwenAuger

     

    From your insight, I came to this scratch formula that seems to work for me.

    Thank you!

     

     

    YTD Values = 
var ActualMonthBeginning = DATE(YEAR(TODAY());MONTH(TODAY());01)
var YearBeginning = DATE(YEAR(MIN(Dates[dates]));01;01)
return
   CALCULATE([Total Values]
      ;FILTER(Values
         ;Values[Dates] < ActualMonthBeginning
         && Values[Dates] >= YearBeginning
      )
   )