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__

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

2.VAR_CurrentDate=

3. MIN( ftCalendar[Date])-1

Thanks!

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