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 the row context.

Desired outpout (Example for the period from 01/01/2026 to 28/02/2026) :

Here the link of PBI file :

https://drive.google.com/file/d/1SxNceUZ97ydlCpjiYSVXlvlBOuRiIQr9/view?usp=sharing

 

Thank you for your help

 

  • 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

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 month ago

    Hi, 

    I am not sure if I understood your question correctly, but please check the attached file down below.

    Average = 
VAR _t =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'Calendar'[Date],
            dProductBatch[Batch],
            "@totalvalue", [Total Value]
        ),
        ALLSELECTED ()
    )
RETURN
    IF ( NOT ISBLANK ( [Total Value] ), AVERAGEX ( _t, [@totalvalue] ) )

     

    10 days moving Average = 
VAR _t =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            'Calendar'[Date],
            dProductBatch[Batch],
            "@totalvalue", [Total Value]
        ),
        ALLSELECTED ()
    )
VAR _moving =
    WINDOW ( 0, REL, 9, REL, _t, ORDERBY ( 'Calendar'[Date], DESC ) )
RETURN
    IF ( NOT ISBLANK ( [Total Value] ), AVERAGEX ( _moving, [@totalvalue] ) )
  • 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
  • PBI_37's avatar
    PBI_37
    Helper I
    1 month ago

    Thank you johnt75 

    It’s perfect. I just adjusted the WINDOW function to have 10 rows overall.