top of page

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


Download sample file


2件のコメント


ゲスト
2023年7月30日

Very helpful post. Does anyone know why we should subtract 1 here?

2.VAR_CurrentDate=

3. MIN( ftCalendar[Date])-1


Thanks!

いいね!

ゲスト
2023年7月29日

Thanks for this post. I used the moving average code to create the calculation I needed today. Works perfectly.

いいね!
bottom of page