Forum Discussion
Need help with TOTALYTD
Hi folks,
I have been trying to figure out how to compare measures year over year for the same time period. In this particular case I'd like to get from Jan 1st to Today (including the same time period for last year and the previous year). I recently discovered that TOTALYTD seems to automatically include the end of the current month. So I noticed the count for this year looked fine because there is no current data past today. The problem comes when comparing data to the previous year (or year before).
The following measures (and variants) work just fine for the current year.
Year to Date = TOTALYTD(COUNT('TEST'[id]),'Calendar'[Date])
Year to Date = TOTALYTD(COUNT('RTEST'[id]),'Calendar'[Date], 'Calendar'[Date] <= TODAY())
Year to Date = CALCULATE(COUNT('TEST'[id]), DATESYTD('Calendar'[Date]))
Year to Date = CALCULATE(COUNT('TEST'[id]), DATESYTD('Calendar'[Date]), 'Calendar'[Date] <= TODAY())
However, I'm having a difficult time applying the same logic for previous years.
YTD Last Year = TOTALYTD(COUNT('TEST'[id]),DATEADD('Calendar'[Date], -1, year))
YTD 2 Years Ago = TOTALYTD(COUNT('TEST'[id]),DATEADD('Calendar'[Date], -2, year))
The above will get everything from Jan 1st 2020 through Oct 31st 2020 when I just need to go to TODAY -1 year.
Logically I feel like I need to use the filter but it doesn't support the TODAY() function with the DATEADD() function.
Any help would be appreciated!
Thanks,
Ok, I found a solution but it's not very elegant.
YTD Last Year = CALCULATE([Year to Date], DATEADD ('Calendar'[Date], -1, YEAR ), 'Calendar'[Date] <= DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))) YTD 2 Years Ago = CALCULATE([Year to Date], DATEADD ('Calendar'[Date], -2, YEAR ), 'Calendar'[Date] <= DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))
5 Replies
- YBFrequent Visitor
Ok, I found a solution but it's not very elegant.
YTD Last Year = CALCULATE([Year to Date], DATEADD ('Calendar'[Date], -1, YEAR ), 'Calendar'[Date] <= DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))) YTD 2 Years Ago = CALCULATE([Year to Date], DATEADD ('Calendar'[Date], -2, YEAR ), 'Calendar'[Date] <= DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))- CNENFRNLCommunity Champion
YTD Last Year = CALCULATE( COUNT( 'TEST'[id] ), DATEADD( DATESYTD( 'Calendar'[Date] ), -1, YEAR ) )- YBFrequent Visitor
Unfortunately, this does not work and it is very similar to what I was already attempting before. This will include all dates in October of last year not just up to the 5th (today).