Dynamic 13 weeks based on WEEK SELECTION [DD-MM-YY]

Anonymous — Fri, 18 Dec 2020 11:51:18 GMT

Hi Power BI Community

I'm trying to create a dynamic 13 weeks selection based on week DD-MM-YY which should dynamically populate 13 weeks data instead of individual selection.

I check the earlier solved DAX query which is mostly based on 13-weeks selection. https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123. Thanks [@AmitChandak]

Currently, I'm using this DAX which is partially doing the job, where I've to select the whole 13 weeks to achieve the desired output, instead, I need one single selection and it should populate the previous 13 weeks data before that without selecting prior 13 week value in the slicer.

In my use case, I'm using Line and Clustered Column Chart that should dynamically change e.g. when I select Week Commencing e.g. 13-12-2020 (Please note I'm using DD-MM-YY not Week number), it should pull data from 13-12-2020 to 20-09-2020 or if I select 20-12-2020 the data range should be till 27-09-2020 so that I can see how my last 13 sales are doing.

As shown in sample datasets and Line https://drive.google.com/file/d/1xk1xlvWiFcGTUH_8X_1R5OwwARVm11NP/view?usp=sharing and Clustered Columncharts. I've attached sample data, DATE table, and DAX measure formula

Please help.

Many Thanks

Re: Dynamic 13 weeks based on WEEK SELECTION [DD-MM-YY]

amitchandak — Fri, 18 Dec 2020 13:02:08 GMT

Anonymous , If need rolling 13 weeks

Rolling 13 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-13*7,Day))

Rolling 13 till last 13 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd('Date'[Date],-13*7,Day)),-13*7,Day))

Last 13 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Last 13 weeks before 13 = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-26 && 'Date'[Week Rank]<=max('Date'[Week Rank]) -13 ))

topic Re: Dynamic 13 weeks based on WEEK SELECTION [DD-MM-YY] in DAX Commands and Tips

Dynamic 13 weeks based on WEEK SELECTION [DD-MM-YY]

Re: Dynamic 13 weeks based on WEEK SELECTION [DD-MM-YY]