topic Re: Rolling average with filter in DAX Commands and Tips

Rolling average with filter

arutsjak90 — Thu, 28 Jan 2021 13:32:53 GMT

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 ) ) )

Re: Rolling average with filter

dedelman_clng — Thu, 28 Jan 2021 14:05:20 GMT

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

Re: Rolling average with filter

arutsjak90 — Thu, 28 Jan 2021 14:49:16 GMT

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

Re: Rolling average with filter

dedelman_clng — Thu, 28 Jan 2021 14:51:35 GMT

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

Re: Rolling average with filter

arutsjak90 — Thu, 28 Jan 2021 15:09:36 GMT

Thanks, but still the same result

Re: Rolling average with filter

dedelman_clng — Thu, 28 Jan 2021 15:12:07 GMT

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

Re: Rolling average with filter

arutsjak90 — Thu, 28 Jan 2021 15:48:06 GMT

I have sent it via private message

Re: Rolling average with filter

dedelman_clng — Thu, 28 Jan 2021 15:52:40 GMT

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

Re: Rolling average with filter

arutsjak90 — Thu, 28 Jan 2021 16:11:30 GMT

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

Thank you!

Is it possible to create calculated column instead of measure?

Re: Rolling average with filter

dedelman_clng — Thu, 28 Jan 2021 17:02:31 GMT

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