Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Community Champion
Community Champion

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

 

Capture.PNG

 



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

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors