Forum Discussion
Calcuate Rolling 4 Weeks Count
Hi,
I need help to calculating Rolling 4 weeks count as a measure [COUNT('SECTIONS'[Section_ID])], data should be represent as below
W1=W1
W2=W1+W2
W3=W1+W2+W3
W4=W1+W2+W3+W4
W5=W2+W3+W4+W5
Thanks in Advance!
Hi bhadraka ,
I made a simple sample
Measure = COUNT('SECTIONS'[Section_ID])Rolling = SUMX(FILTER(ALL('SECTIONS'),[Week rank]<=MAX('SECTIONS'[Week rank])),[Measure])Rolling = SUMX(FILTER(ALL('SECTIONS'),[Week rank]<=MAX('SECTIONS'[Week rank])),[Measure])Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
bhadraka , with week no or week rank. prefer separate week table
Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week]>=max('Date'[Week])-4 && 'Date'[Week]<=max('Date'[Week])))
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
week rank is column in week/date table
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatHi bhadraka ,
I made a simple sample
Measure = COUNT('SECTIONS'[Section_ID])Rolling = SUMX(FILTER(ALL('SECTIONS'),[Week rank]<=MAX('SECTIONS'[Week rank])),[Measure])Rolling = SUMX(FILTER(ALL('SECTIONS'),[Week rank]<=MAX('SECTIONS'[Week rank])),[Measure])Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
