Forum Discussion
TOTALYTD until TODAY
Anonymous
Just clarifying your requirements...
If today is 5-Sep-2017, then what date range do you expect the formula to calculate over?
- 1-Jan-2017 to 5-Sep-2017 (cumulative total as at today, but with years ending on 31-Dec)
- 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)
- 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 :)
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 ) )
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
)
)
Super UserAnonymous
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 :)
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?
doublesHelper II
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 ))
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!
Hi OwenAuger,
Thank you for this solution:
YTD Values = CALCULATE ( TOTALYTD ( [Total Values]; Dates[dates] ); TREATAS ( { TODAY () }; Dates[dates] ) )How would you do this if you need this result for multiple years?
Example:
2016 (01 jan - TODAY) x
2017 (01 jan - TODAY) x
2018 (01 jan - TODAY) x
And so on.
Your formula now gives the result of 01 jan 2016 until TODAY.
Thank you.
- OwenAuger
Just checking exactly what you want here:
Do you want "today" (for example 20 June 2018) to be translated into each calendar year?
So if today is 20 June 2018, then when filtering on 2016, you will get the result for 1 Jan 2016 to 20 June 2016?
thanks for your reply!
This is right, but it's not my intention to filter on a specific year. I want to display all available years in a table with values from 1 Jan to 20 June 2016.
