Moving Average based on number of samples or time

[marco_2020]

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

[Jihwan_Kim]

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

[marco_2020]

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

[Jihwan_Kim]

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.

[Anonymous · Accepted solution]

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.