cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kaydeemlai
Frequent Visitor

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 1
camargos88
Super User III
Super User III

@kaydeemlai ,

 

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]
)

 

Capture.PNG

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors