Forum Discussion
Solved
4wk moving average per group
hello, I am looking to caluclate 4 week moving average on this data set. I want to get the moving average per platform (group) can someone pls help me how i can do this? you can see the excel tab...
you can also try to this
1. create an order column
order = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Platform]=EARLIER('Table'[Platform])&&'Table'[YYWW*]<=EARLIER('Table'[YYWW*])))2. create 4WMA columnColumn =var _start=if('Table'[order]-3<1,1,'Table'[order]-3)return AVERAGEX(FILTER('Table','Table'[Platform]=EARLIER('Table'[Platform])&&'Table'[order]>=_start&&'Table'[order]<=EARLIER('Table'[order])),'Table'[Value])pls see the attachment below
danextian
Super User
Super UserHi ZubinB
Assuming that the four weeks can cross between two years, you will need a separate weeks table that has a column of the weeks' chronological order as you can't simply subtract 3 from the current week. Assuming also that there are only 52 weeks in a year, create this calculated table and relate it to your fact.
Weeks =
VAR _Weeks =
SELECTCOLUMNS ( GENERATESERIES ( 1, 52, 1 ), "Week", [Value] )
VAR _year =
SELECTCOLUMNS ( { 2024, 2025 }, "Year", [Value] )
VAR _crossjoined =
ADDCOLUMNS (
CROSSJOIN ( _year, _Weeks ),
"YYWW", VALUE ( RIGHT ( [Year], 2 ) & FORMAT ( [Week], "00" ) )
)
RETURN
ADDCOLUMNS (
_crossjoined,
"Order", RANKX ( _crossjoined, [YYWW],, asc, DENSE )
)
Create this measure:
4 WMA =
VAR _period =
FILTER (
ALL ( Weeks ),
Weeks[Order]
>= MAX ( Weeks[Order] ) - 3
&& Weeks[Order] <= MAX ( Weeks[Order] )
)
VAR _AVG =
AVERAGEX ( _period, CALCULATE ( SUM ( 'Table'[Value] ) ) )
RETURN
_AVG
Please see the attached sample pbix.
If this isn't what you're looking for, please provide more details as we'll just end up with assumptions.