Understanding the DAX expression for moving averages

January 15, 2023

I stumbled upon this piece of DAX to calculate the 12-day moving average from a table (‘Stock’) that has [Date] and [Close] columns:

MA_12 = 
AVERAGEX(
    DATESBETWEEN(Stock[Date], MAX(Stock[Date]) - 11, MAX(Stock[Date])),
    CALCULATE(SUM(Stock[Close]))
)

I am relatively new to DAX and trying to decipher what is happening behind the scene. I tried to write my own DAX, using my knowledge of filter context and how CALCULATE works, and came up with this version which works as expected:

MA_12_new = 
CALCULATE (
    AVERAGEX( 'Stock', 'Stock'[Close] ),
    DATESINPERIOD(
        'Stock'[Date],
        LASTDATE ( 'Stock'[Date] ),
        -12,
        DAY
    )
)

My questions regarding MA_12:

Why is there no filter parameter specified in CALCULATE? Is CALCULATE somehow "inherits" this filter from DATESBETWEEN expression?
I understand that CALCULATE takes an expression (i.e. an aggregate) as its input. But, I cannot fully wrap my head around the use of SUM. It’s like we sum over the selected 12 days, and then we take an average …. Any clarification is appreciated.

>Solution :

DATESBETWEEN() is creating a virtual table and CALCULATE() is performing context transition for each row of that table. It is a detailed topic and you can read more here: https://www.sqlbi.com/articles/understanding-context-transition/
CALCULATE() returns a scalar (a single value as opposed to a table) so you need some kind of aggregation to be performed. If there is only a single value to return, you could equally use MIN() or MAX() in your first example.