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.
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.