CODEWITHSUNDEEP
powerbidax
math_enthusiast
math_enthusiast

Reputation: 369

Understanding the DAX expression for moving averages

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:

Upvotes: 1

Views: 111

Answers (1)

davidebacci
davidebacci

Reputation: 30219

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

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

Upvotes: 1

Related Questions