Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Community,
I've scoured the internet for this but haven't been able to use any of the solutions posted.
I'm trying to do a YoY calculation that can be used by day, month, quarter, year, all YTD since I have new data coming in every day. I'm having an issue with calculating a year-over-year measure with a partial month. For some reason the 2020 YoY total and YoY% aggregation at the year, quarter, and month level are totally off. The day aggregation and YoY% is correct.
Here is a picture of what I mean along with the DAX measures that I'm using.
Ideally, I would be using this in a KPI as well but it's completely off.
Solved! Go to Solution.
Can you try year behind measure or put a filter to stop it in the max date you have
Total Tickets - Previous Year YTD =
var _max = maxx(table1,dateadd(table1[date],-1,year))
return CALCULATE([Total Tickets],SAMEPERIODLASTYEAR('Calendar (Date)'[Date]), 'Calendar (Date)'[Date] <=_max)
You measures seem fine. Just check the calendar table is marked as date.
But these seem like this year vs last year measure. Looking at data not sure what is off. But Try datesytd or totalytd for YTD
Refer these example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
HI @amitchandak , the total lines for 2020, Quarter 1, and January are wrong. The total should not be 178,382, but 44,043 (sum of Jan 1 to Jan 8, 2019). What formulas should I use to ensure that the aggregation is correct at all those levels?
Can you try year behind measure or put a filter to stop it in the max date you have
Total Tickets - Previous Year YTD =
var _max = maxx(table1,dateadd(table1[date],-1,year))
return CALCULATE([Total Tickets],SAMEPERIODLASTYEAR('Calendar (Date)'[Date]), 'Calendar (Date)'[Date] <=_max)
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |