Forum Discussion

arutsjak90's avatar
arutsjak90
Icon for Helper I rankHelper I
5 years ago
Solved

Rolling average with filter

Hi, I am trying to create 3months back rolling average with filter, becaue it needs to be rolling average per Plant. What I have created (or edited)  below. *The table I'm working on was created wit...
average
Rolling Months
  • dedelman_clng's avatar
    dedelman_clng
    5 years ago

    Hi arutsjak90 -

     

    Make sure you have a calendar table, linked to the "Fixed Date" column in your table, and then try this code

     

    Rolling 3 Mo Avg =
VAR __ThisMonth =
    MAX ( COGS[FixedDate] )
VAR __3MoBack =
    EDATE ( __ThisMonth, -3 ) + 1
RETURN
    CALCULATE (
        SUM ( COGS[Total COGS] ),
        ALLEXCEPT ( COGS, COGS[Plant] ),
        DATESBETWEEN ( DateTab[Date], __3MoBack, __ThisMonth )
    ) / 3

     

     

    Hope this helps

    David