topic Re: Moving Average based on number of samples or time in DAX Commands and Tips

Moving Average based on number of samples or time

marco_2020 — Thu, 16 Jun 2022 15:13:46 GMT

Hello, I have found many threads about moving average but they are all based on date filters (e.g. moving average based on a time windows of at least 1 day). In my report I need to implement a moving average based on a specific number of samples (e.g. buffer size of 10 samples) or based on hours (time window of 3 hours).

How is this possible?

Thank you in advance

Re: Moving Average based on number of samples or time

Jihwan_Kim — Thu, 16 Jun 2022 17:50:59 GMT

Hi,

I am not sure if I understood your question correctly.

I tried to create a sample pbix file like below, and the below sample is showing last 10 samples moving avg.

Moving avg of last 10 index measure: = AVERAGEX ( TOPN ( 10, FILTER ( ALL ( Data ), Data[Index] <= MAX ( Data[Index] ) ), Data[Index], DESC ), Data[Value] )

Re: Moving Average based on number of samples or time

marco_2020 — Fri, 17 Jun 2022 07:00:28 GMT

Hello,

really thank you.

My last concern is regarding computing moving average in a dataset over datetime column considering for example last 3 hours as time window. Is this possible?

Thanks

Marco

Re: Moving Average based on number of samples or time

Jihwan_Kim — Fri, 17 Jun 2022 09:36:32 GMT

Hi,

I do not know how your data model looks like, but I think it is possible by using dim-time table for the calculation of 3 hrs segment.

Thank you.

Re: Moving Average based on number of samples or time

Anonymous — Wed, 22 Jun 2022 05:42:15 GMT

Hi marco_2020 ,

You can try the following dax:

Meaure = VAR currDate = MAX ( Table[LOAD_END_TIME] ) RETURN CALCULATE ( AVERAGE ( Table[SHOVEL_TRUCK_DIFFERENCE] ), FILTER ( ALLSELECTED ( Table ), [LOAD_END_TIME] <= currDate && DATEDIFF ( [LOAD_END_TIME], currDate, SECOND ) <= 1800 ) )

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.