Forum Discussion
Syndicate_Admin
Administrator
AdministratorRolling 4 weeks calculation
Hello I am calculating Rolling 4 weeks based on data below W1-W1 W2-W1+W2 W3-W1+W2+W3 W4-W1+W2+W3+W4 W5-W2+W3+W4+W5 (starting from W2) ..... W1 is 01-January-2020 to 07-January-2020 W2 is 08...
amitchandak
Super User
Super UserSyndicate_Admin, With help from separate week table or Year week /Date and week rank
Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week]>=max('Date'[Week])-4 && 'Date'[Week]<=max('Date'[Week])))
With Week year
new column in week year/date table
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures
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 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))