Forum Discussion
Helper IIYear Over Year Percent Change By Week
Hoping someone can point me in the right direction here...I need to take the data that we have and compare it year over year using our definition of a week. Unlike the traditional Week format we only look at 9 weeks, and the dates that these weeks start change year over year so I can not do it using any YTD calculations. A sample of the Dates/Week data can be found attached as an image. The People data has to originate from a sum, as it counts people by day. "SAMEPERIODLASTYEAR" will not work because the dates for the weeks fluctuate year over year.
I need to be able to see the Year Over Year Percent change by week, so my intended output looks something like this...
Year Week People Percent Change
2019 Week 1 900 -10%
2018 Week 1 1000 11%
2017 Week 1 900 100%
2016 Week 1 450
2019 Week 2 500 29%
2018 Week 2 700 133%
2017 Week 2 300 63%
2016 Week 2 800
Any suggestions would be great thanks.
Well it looks like you're already part way there, since you have the week number. Instead of using the builtin SAMEPERIODLASTYEAR calculations, you get to make your own!
You can take a look at this answer for a starting point of how to get a difference between the current row value and the previous row value: https://community.powerbi.com/t5/Desktop/Power-BI-Running-Totals-Kinda/m-p/719274#M347104
Your case should be simpler than that one, but it has the same idea of finding the difference between the number of people in the current year and the number of people in the previous year while still staying within the same group of weeks.
2 Replies
Doing weekly comparisons always adds in a layer of complexity. I'd check out this article from the sqlbi guys:
https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/
- Cmcmahan
Resident Rockstar
Well it looks like you're already part way there, since you have the week number. Instead of using the builtin SAMEPERIODLASTYEAR calculations, you get to make your own!
You can take a look at this answer for a starting point of how to get a difference between the current row value and the previous row value: https://community.powerbi.com/t5/Desktop/Power-BI-Running-Totals-Kinda/m-p/719274#M347104
Your case should be simpler than that one, but it has the same idea of finding the difference between the number of people in the current year and the number of people in the previous year while still staying within the same group of weeks.
