Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
1 | A | 100 |
1 | B | 200 |
1 | C | 300 |
2 | A | 150 |
2 | B | 200 |
2 | C | 300 |
3 | A | 150 |
3 | B | 200 |
3 | C | 300 |
I need it in this view. I just showed 2 Week MA but would need it for past 4 weeks.
Week | SUM Sales | 2 Week MA |
1 | 600 | 600 |
2 | 650 | 625 |
3 | 750 | 700 |
@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]
)
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |