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

Thank you for your time, but it is not working.. 
after entering this code, I get the sum of the whole Total COGS column divided by 3, not splited into plants and not rolling, very strange

 

 

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

You need to create it as a measure, not as a column.

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

    Thanks, but still the same result

     

     

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

      Please share a copy of your pbix with sensitive data removed. Dropbox, Google Drive, OneDrive, GitHub, etc.

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

        I have sent it via private message