Forum Discussion
arutsjak90
Helper I
Helper IRolling 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...
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 ) ) / 3Hope this helps
David
dedelman_clng
Community Champion
Community ChampionHi 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
arutsjak90Helper I
Helper IThank 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
You need to create it as a measure, not as a column.
- arutsjak90
Thanks, but still the same result
- dedelman_clng
Please share a copy of your pbix with sensitive data removed. Dropbox, Google Drive, OneDrive, GitHub, etc.