Search code examples
daxmoving-averagerolling-average

Calculating Moving Average for N Months in DAX Power BI

I have a measure that calculates Moving Average for 3 months:

     Moving_Avg_3_Months = AVERAGEX(DATESINPERIOD('Calendar FY'[Date], 
                                    LASTDATE('Calendar FY'[Date]), -3, MONTH),[CUS Revenue Credible All])

Is it possible to create a measure that would calculate Moving Average for my [CUS Revenue Credible All] - but for N months. Where N = 3 or N = 6 or N = whatever number I'd like?

Solution

  • If you create a new table with the different values for moving average you want to use eg. TableMovingAverage: [-3,-6,-12,-24,...,N]
    and modify you DAX formula like this:

    Moving_Avg_3_Months = 
    AVERAGEX(
       DATESINPERIOD('Calendar FY'[Date], 
          LASTDATE('Calendar FY'[Date]), 
          SELECTEDVALUE('TableMovingAverage', -3), 
          MONTH), 
       [CUS Revenue Credible All])

    SELECTEDVALUE returns a scalar if only one value is in the specified table, otherwise it return a default value -3 in this case.
    If you filter TableMovingAverage you can switch between different moving averages