Forum Discussion

PBI_37's avatar
PBI_37
Helper I
1 month ago
Solved

Average and Moving Average

Hello everyone, I have a dataset with products and batches. In a visual such as a table, I would like to calculate the average and the moving average of the last 10 values. I have an issue with th...
  • johnt75's avatar
    johnt75
    1 month ago

    You can use the WINDOW function to get the moving average.

    Moving Average =
VAR _Base =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'Calendar'[Date],
            dProductBatch[Batch],
            "@value", [Total Value]
        ),
        ALLSELECTED ( 'Calendar' ),
        ALL ( dProductBatch )
    )
VAR _Window =
    WINDOW (
        -10,
        REL,
        0,
        REL,
        _Base,
        ORDERBY ( 'Calendar'[Date], ASC, dProductBatch[Batch], ASC )
    )
VAR Result =
    AVERAGEX ( _Window, [@value] )
RETURN
    Result
johnt75's avatar
johnt75
Super User
1 month ago

You can use the WINDOW function to get the moving average.

Moving Average =
VAR _Base =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'Calendar'[Date],
            dProductBatch[Batch],
            "@value", [Total Value]
        ),
        ALLSELECTED ( 'Calendar' ),
        ALL ( dProductBatch )
    )
VAR _Window =
    WINDOW (
        -10,
        REL,
        0,
        REL,
        _Base,
        ORDERBY ( 'Calendar'[Date], ASC, dProductBatch[Batch], ASC )
    )
VAR Result =
    AVERAGEX ( _Window, [@value] )
RETURN
    Result