Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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())))
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())))
YTD Last Year =
CALCULATE(
COUNT( 'TEST'[id] ),
DATEADD( DATESYTD( 'Calendar'[Date] ), -1, YEAR )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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).
YTD Last Year =
CALCULATE(
COUNT( 'TEST'[id] ),
DATEADD(
CALCULATETABLE( DATESYTD( 'Calendar'[Date] ), 'calendar'[Date] <= today ),
-1,
YEAR
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks, I gave it a try but it still didn't give me the desired result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |