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
     
Abhilash_P's avatar
Abhilash_P
Icon for Super User rankSuper User
1 year ago

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