Forum Discussion
Running 13 weeks total based on selected quarter
I want to calculate the running 13 weeks RP Qty sum.
For Example if i select Q2 FY22 in the version quarter filter , W1 should have the RP Qty data for next 12 weeks including W1 , W2 should have the data for next 12 weeks including W2 and so on! W1 is dervied from the cersion quarter(Custom Column)
Kindly help and thanks in Advance
Anonymous , if you can create week rank (if need across qtr) then oon week start date else on on qtr week no
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatLast 12 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])&& 'Date'[Week Rank]<=max('Date'[Week Rank]) +12 ))
3 Replies
- amitchandak
Super User
Anonymous , if you can create week rank (if need across qtr) then oon week start date else on on qtr week no
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatLast 12 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])&& 'Date'[Week Rank]<=max('Date'[Week Rank]) +12 ))