Rolling average with filter

[arutsjak90]

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

COGSTotal = SUMMARIZE(COGS,COGS[TotalByPlantKey],COGS[Plant],COGS[FixedDate],"Total COGS",SUM(COGS[ CostOfSale]))

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

*A circular error appears

3MonthsRollingAverage =

VAR LastDate_ =

LASTDATE ( COGSTotal[FixedDate])

RETURN

CALCULATE (

AVERAGEX ( VALUES (COGSTotal[FixedDate]), CALCULATE ( SUM ( COGSTotal[Total COGS] ) ) ),

FILTER (

COGSTotal,

COGSTotal[Plant]=EARLIER(COGSTotal[Plant]) &

[FixedDate] <= LastDate_ && [FixedDate] > DATEADD ( LastDate_, -3, MONTH ) ) )

[dedelman_clng · Accepted solution]

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]

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]

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.

[arutsjak90]

I have sent it via private message

[dedelman_clng]

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

[arutsjak90]

https://drive.google.com/drive/folders/16GoTloQOeYLuvgu6eMl6M0LSgKA4iJzj?usp=sharing

Thank you! 

Is it possible to create calculated column instead of measure?

[dedelman_clng]

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