Forum Discussion

ZubinB's avatar
ZubinB
Frequent Visitor
1 year ago
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...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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
     
techies's avatar
techies
Super User
1 year ago

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's avatar
ZubinB
Frequent Visitor
1 year ago

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