Forum Discussion

Helper 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

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

dedelman_clng

Community Champion

5 years ago

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

arutsjak90

Helper I

5 years ago

Thanks, but still the same result

dedelman_clng
Community Champion
5 years ago
Please share a copy of your pbix with sensitive data removed. Dropbox, Google Drive, OneDrive, GitHub, etc.
- arutsjak90
  Helper I
  5 years ago
  I have sent it via private message
  - dedelman_clng
    Community 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