Dynamic Last 5 Week based on selected date or Week date

[mohittimpus]

Hello All,

I have a date table. I need last 5 week dynamically based on selected week date or date.

below i am sharing image:

Here on slicer value will be seleced 08/30/2020.

Below, I am sharing Power BI File and excel file:

PBIX File:  https://drive.google.com/file/d/1p-ryWXkF0kx3VkRRPtiRjccOeja8QohZ/view?usp=sharing

Excel File:  https://drive.google.com/file/d/1bqbU7A8CohGBCym_ecPnkjeQtKxq1I6m/view?usp=sharing

[amitchandak · Accepted solution]

@mohittimpus , have following columns in your date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

Try measure like 
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)))
Last 5 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-5 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

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...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.