Moving Average column

[SDPowerBI123]

hello all i am trying to do a moving average column but having no success.

i am just trying for a moving average of 2 days, but the column seem to only show current day. i also shown my dax there for the calculated column in case you need to see. i can type it below for you.

Moving Average X Days Prior =

CALCULATE(

    AVERAGEX('SPY 1 Years Daily', 'SPY 1 Years Daily'[Close]),

    DATESINPERIOD('SPY 1 Years Daily'[Date], LASTDATE('SPY 1 Years Daily'[Date]), -2, DAY

    )

)

[sjoerdvn]

Try this:

Moving Average 2 Days = VAR ld = LASTDATE('SPY 1 Years Daily'[Date])
VAR fd =DATESINPERIOD('SPY 1 Years Daily'[Date], ld,-2, DAY)
RETURN CALCULATE(AVERAGE('SPY 1 Years Daily'[Close]),ALL('SPY 1 Years Daily'),fd)

[rajendraongole1 · Accepted solution]

Hi @SDPowerBI123 - The issue with your DAX formula for the moving average is that it calculates the average over only the current day

can you check the below modified one to get the current and previous days

Moving Average 2 Days =
CALCULATE(
AVERAGEX(
DATESINPERIOD(
'SPY 1 Years Daily'[Date],
LASTDATE('SPY 1 Years Daily'[Date]),
-2,
DAY
),
'SPY 1 Years Daily'[Close]
)
)

Hope this helps