Sum moving by week groups

[Dodzik]

Hello,

I am looking for a help with creation of calculated table which contains Orders data(multiple measures) aggregated by 13 weeks back. Please see an example of one of these calculation below:

In this example you can see that value of desired measure is calculated as sum moving on WeekYear attribute which is available in date dimension. Value for 201825 is a summary of weeks from 25 to 13(25-13), 201824 is 24 to 12 etc.. This of course has to be able to work dynamically when it comes to turn of the year.

[TomMartens · Accepted solution]

Hey,

maybe this measure gives you an idea:

Moving Sum = 
CALCULATE(
 SUM('Fact'[Value])
 ,FILTER(ALL('Calendar')
 ,'Calendar'[RunningPeriod] <= MAX('Calendar'[RunningPeriod]) && 'Calendar'[RunningPeriod] >= MAX('Calendar'[RunningPeriod]) - 1))

Just replace the table names, replace the column reference "RunningPeriod" by your columnname WeekYear and the step by number of weeks.

This should do the trick.

Regards

Tom