Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Need help calculating Moving Average on Aggregate level based on non date fields

Hello Team, 

 

I have a dataset which consists of 48 weeks of data for each subscriber. I would like to calculate 4 rolling average by week (aggregated by week for all subscribers. i.e. Week 1 sum should include sum of values for all dealers for week 1, then week 2 and so on). Please guide me on the best way to achieve this. Here is how my data looks 

 

Week Subscriber ID Sales
1A100
1B200
1C300
2A150
2B200
2C300
3A150
3B200
3C300

 

I need it in this view. I just showed 2 Week MA but would need it for past 4 weeks.

 

Week SUM Sales2 Week MA
1600600
2650625
3750700

1 Reply

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous ,

     

    Try this measure:

     

    _MA_4 Weeks = 
VAR _week = SELECTEDVALUE('Table'[Week ])
RETURN
    AVERAGEX(
        SUMMARIZE(
            FILTER(
                ALL('Table'), 
                'Table'[Week ] >= _week - 3 && 'Table'[Week ] <= _week
            ), 'Table'[Week ], 
        "Total", SUM('Table'[Sales])
        ), 
    [Total]
)