Mudassir
Thanks for the challenge - and for the guidance on using 11 January as ‘today’ for the sake of the calculations.
As this is a dynamic solution I thought it best to publish the result to Power BI service - hope this link works.
Of course you can’t see the DAX from that - so here is the PBI file:
eDNA POTW 5 - Chris H.pbix (497.7 KB)
Summary
My approach was to use the “Week offset” column from the date table to provide the weekly basis of the calculation.
As ‘today’ was actually 11 January 2021, I set a ‘Reference date’ measure to this date and a branched measure to calculate the number of weeks offset from actual today to that reference date. I thought about making the reference date dynamic as I can see some value in being able to run this analysis from alternative starting points - but didn’t get round to making this work.
The DAX for the last N weeks and the N weeks preceding those N weeks was not particularly tricky - just a CALCULATE with a FILTER on the weeks being referred to - the hard bit was trying to keep all the Ns straight!
Using Unicode characters in a measure was new to me. I found all of the Unicode characters on - https://unicode-table.com/en/ - but it took some trial and error to figure out which number I needed in the DAX expression (hint: not the Unicode number!)
Using small multiples tables was also a nice new tool to be introduced to - I never would have found it hidden in the Previews without it being suggested here - it could well have been useful for the COVID dashboard challenge. I added a fourth country to the Top N filter so as to avoid a blank corner of the visualisation.
All the best,
Chris