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

     

     

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

I have sent it via private message

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
5 years ago

Please post the link in the thread, not PM, in case others want to look at it or help.

 

That being said, you did not create the proper relationships in your model, as instructed. This will cause the measure code to not work correctly.

 

Link "COSG[Fixed Date]" to DateTab[Date].

 

Hope this helps

David

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    5 years ago

    I would not recommend creating it as a calcaulted column. Measures can be used in mostly the same way as columns and are more dynamic.

     

    If you run into an issue using the measure in further calculations, please start a new thread instead of continuing this one.

     

    David