Comparing - over 3 data sets for weekly comparison

[VaishnaviGandhi]

Hi All, 

I have a report coming in with updates and addition every week. It has 80% similarity and 20% new data. 

for eg - Week 01,02,03 data with me in different files and imported into PBI desktop as seperated data. now i need a week over week comparison to see the progress and changes on different status. one thing i can do is manually create data in excel by adding all weeks data into a Master file and then filter the data while creating a report so the all weeks visuals interact with one click. When interacting the visuals I have a slicer with only Week03 common data of countries, so its only interacting with W03 obviously, I want it to interact with W01 and W02 as well. 

My question -  is there a way to create a relationship using a measure in power BI instead of creating a master data for weeks?

Thanks

Vaishnavi

[amitchandak]

@VaishnaviGandhi , You can create a relation in measure. But I would advise having a common week/Date table and using week Rank. Measure level join might create a complex formula and slow down performance

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Power BI — Week on Week and WTD
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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8