Moving Average

[Anonymous]

Hi,

I have a table having MonthYr and Value as mentioned below and I wanted to calculate Moving Average as mentioned expected output.

Moth Yr Value Expected output
Feb-16 14
Mar-16 21 17.5
Apr-16 11 16
May-16 43 27
Jun-16 17 30
Jul-16 19 18
Aug-16 31 25
Sep-16 23 27
Oct-16 12 17.5

[v-yulgu-msft · Accepted solution]

Hi, @Anonymous

Add a calculated column in table

CountRows2 = 1

Create measures following the below formulas:

RunningRows =
CALCULATE (
    SUM ( 'Moving Agerage'[CountRows2] ),
    FILTER (
        ALL ( 'Moving Agerage' ),
        'Moving Agerage'[MonthYr] <= MAX ( 'Moving Agerage'[MonthYr] )
    )
)

RunningTotal =
CALCULATE (
    SUM ( 'Moving Agerage'[Value] ),
    FILTER (
        ALL ( 'Moving Agerage' ),
        'Moving Agerage'[MonthYr] <= MAX ( 'Moving Agerage'[MonthYr] )
    )
)

MovingAverage = [RunningTotal]/[RunningRows]

Best regards,
Yuliana Gu