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

     

     

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
9 years ago

Anonymous

 

That's good if the formula gives the desired result!

 

I would just propose this simper alternative:

Given that your year_end_date is the default of 31-Dec, and you are simply calculating a cumulative total as at "today", you can get away with a simpler formula.

 

Also, it would be best practice to apply date filters only to the Dates table. I am assuming there is a relationship between Values[Dates] and Dates[dates].

 

YTD Values =
CALCULATE (
    TOTALYTD ( [Total Values]; Dates[dates] );
    TREATAS ( { TODAY () }; Dates[dates] )
)

 

You should only need to write YTD time intelligence logic if you have a 'flexible' year-end date, but in your case it is just the date filter that needs to be flexible (set equal to TODAY()).

 

Owen :)

taylorrc's avatar
taylorrc
Regular Visitor
8 years ago

This worked great for me. Thanks!

 

I have a question thought. I'm also comparing to same period last year. I'm using the formula:

 

Last Year YTD=Calulate ([CurrentYearYTD]), SAMEPERIODLASTYEAR(DATESYTD('Table'[Date])))

 

This is returning the same value at the CurrentYearYTD calcluation. Any suggestions to make this comparison?

  • doubles's avatar
    doubles
    Icon for Helper II rankHelper II
    8 years ago

    I was trying to do the same thing, only over 4 years. I ended up creating a calendar table with a DayOfYear column and then filtering on data with a dayofyear less than or equal to the day numer of  today, so I was able to compare the sums up to a particular day  in each year.

     

    SumAmtYtd = CALCULATE(SUM(Table[Amount]),FILTER(Dates,Dates[DayNoOfYear] <= DATEDIFF ( DATE ( YEAR ( TODAY()), 1, 1 ), TODAY(), DAY ) + 1 ))

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Is the column of the "DayOfYear" = Day('Calender'[Date])? 

      I tried your solution, but the SumAmtYtd returns the same value of the CurrentAmt. Could you please give me more detiails? 

      Thanks a lot!