This blog is to discuss how to calculate moving or rolling average for the past X months.

## Source Data

## Understanding DATESINPERIOD() DAX Function

The important function used that we will use to calculate past X months or days of some aggregation is DATESINPERIOD() DAX function.

DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)

Lets start with past 1 day transactions count to first understand how the DATESINPERIOD() DAX function works.

**Note:** I have a calendar table which is called MyDates and it has 1:many relationship with the fact table PrevMonth.

Create the following measure.

Past X Days Count := VAR PastCount = CALCULATE ( COUNT ( PrevMonth[Amount] ), DATESINPERIOD ( MyDates[Date], LASTDATE ( MyDates[Date] ), -1, DAY ) ) RETURN IF ( MIN ( PrevMonth[Amount] ) > 0, PastCount )

**Note** that in the below visuals the Date column is from the MyDates calendar table.

The important item to note in the results is that when we use -1 as the number of interval DAX includes only that particular day. So for 2nd of January when the number of interval is -1 the result is 2nd of January (NOT 1st of January).

The IF condition in the RETURN statement essentially removes dates with no sales to get a cleaner result as displayed above.

This difference will become very important when we start to use MONTHS for number of interval as how the number of interval is interpreted is key to understanding the results. Often times this will cause confusion as we tend to forget the calculation is inclusive.

So let’s look at the results for the above example with MONTHS as the interval in the below measure.

Past X Months Count := VAR PastCount = CALCULATE ( COUNT ( PrevMonth[Amount] ), DATESINPERIOD ( MyDates[Date], LASTDATE ( MyDates[Date] ), -1, MONTH ) ) RETURN IF ( MIN ( PrevMonth[Amount] ) > 0, PastCount )

When the number of interval is -1 and interval is MONTH, as you can see in the above image, on 2nd January 2018 the ‘Past X Months Count’ is 3 which is the total number of transaction for January 2018.

So when interpreting the results for past one month for a given date, it is for that particular month only.

## Moving / Rolling Average Calculation Using DAX

To calculate the moving average, we use the similar measure to above with using AVERAGE() DAX function.

Past X Months Average :=

VAR PastAverage =

CALCULATE (

AVERAGE ( PrevMonth[Amount] ),

DATESINPERIOD ( MyDates[Date], LASTDATE ( MyDates[Date] ), -1, MONTH )

)

RETURN

IF ( MIN ( PrevMonth[Amount] ) > 0, PastAverage )

As you can see in the above image, when the number of interval is -1 and interval is MONTH in the DATESINPERIOD() function, the Result section shows the average for the given month.

Below is the same information summarised to month.

**So average for last one month for a given month will be the average for that month.**

Now let’s see the same formula and results when the interval is -2 which is for past 2 months as in below measure.

Past X Months Average :=

VAR PastAverage =

CALCULATE (

AVERAGE ( PrevMonth[Amount] ),

DATESINPERIOD ( MyDates[Date], LASTDATE ( MyDates[Date] ), -2, MONTH )

)

RETURN

IF ( MIN ( PrevMonth[Amount] ) > 0, PastAverage )

So when we say average for the rolling past two months that means the given month and the previous month.

**Once you understand this, then interpreting the results will be accurate.**

## Making the selection of past X months dynamic or user selection based

You can create a table (MyMonths) with as below and create a slicer.

The DAX measure to use would be:

Past X Months Average :=

VAR PastAverage =

CALCULATE (

AVERAGE ( PrevMonth[Amount] ),

DATESINPERIOD (

MyDates[Date],

LASTDATE ( MyDates[Date] ),

SELECTEDVALUE ( MyMonthsTable[Past Months], 0 ),

MONTH

)

)

RETURN

IF ( MIN ( PrevMonth[Amount] ) > 0, PastAverage )

Now you can select the number of intervals to be forward (positive numbers) or past (negative numbers). So for past two months you can select -2 in the slicer.

## Other time intelligence functions

You can use other DAX time intelligence functions similar to the above.

e.g.

Total YTD :=

VAR PrevYTD =

TOTALYTD (

SUM ( PrevMonth[Amount] ),

DATESINPERIOD (

MyDates[Date],

LASTDATE ( MyDates[Date] ),

SELECTEDVALUE ( MyMonthsTable[Past Months], 0 ),

MONTH

)

)

RETURN

IF ( MIN ( PrevMonth[Amount] ) > 0, PrevYTD )

## Leave a Reply