Forum Discussion
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 with the Summarize function
| Total COGS | FixedDate | Plant | 3monthsRollingAverage |
| 2 | 01/01/2020 | 1 | |
| 3 | 02/01/2020 | 1 | |
| 2 | 03/01/2020 | 1 | 2.33333 |
| 5 | 04/01/2020 | 1 | 3.33333 |
| 4 | 01/01/2020 | 2 | |
| 2 | 02/01/2020 | 2 | |
| 3 | 03/01/2020 | 2 | 3 |
| 3 | 04/01/2020 | 2 | 4 |
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
9 Replies
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
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 strangeYou need to create it as a measure, not as a column.
