Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Sum moving by week groups

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:

 

example

 

 

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.

Tutorial Requests
  • TomMartens's avatar
    TomMartens
    8 years ago

    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

1 Reply

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    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