4wk moving average per group

[ZubinB]

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 table below with the 4wma calculated per platform from wk44 to wk06. how can i do this in powerbi?

Thanks for your help. Appreciate it...

YYWW*	Value	Platform	4WMA
2444	1.587302	A	1.587302
2445	0.198413	A	0.892857
2446	1.388889	A	1.058201
2447	0.992063	A	1.041667
2448	0	A	0.644841
2449	0	A	0.595238
2450	0	A	0.248016
2451	0	A	0
2452	0	A	0
2501	0	A	0
2502	0	A	0
2503	18.60119	A	4.650298
2504	5.803571	A	6.10119
2505	13.07292	A	9.36942
2506	2.752976	A	10.05766
2507	1.264881	A	5.723586
2444	0	B	0
2445	0.297619	B	0.14881
2446	0	B	0.099206
2447	0	B	0.074405
2448	0	B	0.074405
2449	0	B	0
2450	0	B	0
2451	0	B	0
2452	0	B	0
2501	0	B	0
2502	0	B	0
2503	2.083333	B	0.520833
2504	5.555556	B	1.909722
2505	0	B	1.909722
2506	2.380952	B	2.50496
2444	0	C	0
2445	0	C	0
2446	0	C	0
2447	0.297619	C	0.074405
2448	0	C	0.074405
2449	0	C	0.074405
2450	0	C	0.074405

[ryan_mayu · Accepted solution]

@ZubinB 

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 column

Column =

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

[ZubinB]

Thanks a lot @ryan_mayu Ryan. I was exactly trying to do that... create an index first and create moving average .. but i was struggling. thanks for the solution.. i really appreciate it..

[ryan_mayu]

you are welcome

[techies]

Hi @ZubinB please try this

VAR CurrentWeek = SELECTEDVALUE(Sheet1[yyww])

VAR CurrentPlatform = SELECTEDVALUE(Sheet1[Platform])

VAR MovingAverage =

    AVERAGEX(

        FILTER(

            ALLSELECTED(Sheet1),

            Sheet1[Platform] = CurrentPlatform &&

            Sheet1[yyww] <= CurrentWeek &&

            Sheet1[yyww] >= CurrentWeek - 3

        ),

        Sheet1[Value]

    )

RETURN

IF(ISBLANK(MovingAverage), 0, MovingAverage)

[ZubinB]

Thanks @techies  for helping.. I tried your solution. it is returning me all zeroes. Dont know why. Thanks for the help. I already got the solution from @ryan_mayu 

[danextian]

Hi @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.

[ZubinB]

Thanks @danextian  for the solution. Let me try this over and get back to you. 

[ZubinB]

Hi @Abhilash_P  

Thank you for taking the time in answering the question.

It didn't return any error but it dint give any numbers. it created 4WMA column with blanks.

what am i doing wrong? sorry im a beginner in powerbi..

Thanks

[Abhilash_P]

Hi @ZubinB ,

Can you check with below DAX function

4WMA =
VAR CurrentWeek = SELECTEDVALUE('Table'[YYWW]) -- To Get current YYWW
VAR CurrentPlatform = SELECTEDVALUE('Table'[Platform]) -- To Get current Platform

RETURN
CALCULATE(
AVERAGE('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[Platform] = CurrentPlatform &&
'Table'[YYWW] <= CurrentWeek &&
'Table'[YYWW] > CurrentWeek - 4
)
)