% difference each year based on week number and how to filter it correctly.

[Anonymous]

Hi

I have a table with columns such as Date ( date hierarchy ), Sum_of_Value ( data ), Weeknr ( in format "202033" would be week 33 in year 2020 ) ,SuperGroup ( items ) and Chain ( Store1 and Store2 ).

I need to create a matrix visual with Row: ( SuperGroup ) and Columns: ( Chain, Weeknr, Date [year]) with Values: ( Sum_of_Value ).

Problem nr1: I need the Sum_of_Value part to show % difference from last year same week and so it would be dynamic and always show data of the latest week we have in 2020. Now the latest data would be in week 202033. I need to show % difference between 202033 and 201933 in column Date [year] 2020. In column Date [year] 2019 I need it to show data difference in Weeknr 201933 and 201833. ( In 2019 the latest week is not 33 but I need it to show according to year 2020 the same week ). 

Problem nr2: How can I correctly calculate to show % difference between those weeks? In quick measure Year-over-year change I can't choose my Weeknr column as Date since it's not a date column ( can't change it to date either or it'll just change the numbers "202033" becaomes something like "20/12/2045" ) and picking Date column gives me not correct numbers ( most likely because week 33 isn't the same date in 2020 as it is in 2019 ). 
I tried makeing a new column with only "Year" and use DAX:

% CHANGE = CALCULATE(SUM('MEE TSR_DATA_Weekly'[tsrw_Sum_of_VALUE]),FILTER('MEE TSR_DATA_Weekly','MEE TSR_DATA_Weekly'[Year] = 2020)) / CALCULATE(SUM('MEE TSR_DATA_Weekly'[tsrw_Sum_of_VALUE]),FILTER('MEE TSR_DATA_Weekly','MEE TSR_DATA_Weekly'[Year] = 2019)) - 1​

But since my column has to be filtered ( atm manually ) to latest week '202033' it shows wrong numbers again ( I'm guessing its the percentage difference between whole years worth of data [2020] and [2019] not each week seperately. 

So far only way I got correct value was by using Qucik measure "Percentage difference from filtered value" on Base value I used my Sum_of_Value column and on filter value I used my Weeknr column and picked 201933. Now when I filter to show only week 202033 on my matrix visual the data is correct for year 2020 but this way I can't add year 2019. I made a duplicate of the table and filtered out every data up until 201933 and did the same quick measure as before but with filter 201833 I was able to get correct 2019 data as well but I can only show this in 2 different visuals so I hid 1 under other and made it look like it was all together but they're not and also this way its not dynamic and I have to change settings everytime new weeks data comes in.

[amitchandak · Accepted solution]

@Anonymous , refer my week vs week blog for this. It uses rank concept for that

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Example

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)	
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

[Anonymous]

Thank you

This worked and I got my data correctly working now as it should 🙂