Year Over Year Percent Change By Week

[chq]

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.

[Cmcmahan · Accepted solution]

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.

[Anonymous]

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/