Moving Average using DAX, p2

In the earlier article, we learned how to calculate the moving average at a day level (30 days). It is since the date granularity is at a day level. This article explains how we can calculate the moving average at a month or a quarter level when the date granularity is at a day level.

We have included a calendar table in our data model:

And created a relationship with the Order table:

Unlike the earlier example, we cannot use the AVERAGEX formula due to the granularity. We use the mathematical formula for average (sum of numbers divided by count of numbers).

Moving Average by Month

Let us say we are calculating the moving average of the past three months. For this, we sum the revenue for the previous three months and divide by the month count (3).

To calculate the count of the month, we use the formula DISTINCTCOUNT on the month column of the calendar table.

1. Moving Average _M = 
2. VAR _CurrentDate =
3.      MIN ( ftCalendar[Date] ) - 1
4. VAR _FilterDate =
5.      DATESINPERIOD ( ftCalendar[Date], _CurrentDate, -3, MONTH )
6. VAR _Monthly =
7.      CALCULATE ( [Revenue], _FilterDate )
8. VAR _MonthCount =
9.      CALCULATE ( DISTINCTCOUNT ( ftCalendar[Month] ), _FilterDate )
10.VAR _Average =
11.     DIVIDE ( _Monthly, _MonthCount )
12.RETURN
13. _Average

Moving Average by Quarter

Similarly, to calculate the moving average of the last three quarters, we replace month components by the quarter

1. Moving Average _Q = 
2. VAR _CurrentDate =
3.      MIN ( ftCalendar[Date] ) - 1
4. VAR _FilterDate =
5.      DATESINPERIOD ( ftCalendar[Date], _CurrentDate, -3, QUARTER )
6. VAR _Quarterly =
7.      CALCULATE ( [Revenue], _FilterDate )
8. VAR _QuarterCount =
9.      CALCULATE ( DISTINCTCOUNT ( ftCalendar[YearQtr] ), _FilterDate)
10. VAR _Average =
11.     DIVIDE ( _Quarterly, _QuarterCount )
12.RETURN
13. _Average