Dynamic moving average using DAX in Power BI

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

Source Data

Capture

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] )-1DAY )
    )
RETURN
    IF ( MIN ( PrevMonth[Amount] ) > 0PastCount )

 

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

Capture

 

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] )-1MONTH )
    )
RETURN
    IF ( MIN ( PrevMonth[Amount] ) > 0PastCount )

Capture

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] )-1MONTH )
    )
RETURN
    IF ( MIN ( PrevMonth[Amount] ) > 0PastAverage )

Capture

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.

Capture

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] )-2MONTH )
    )
RETURN
    IF ( MIN ( PrevMonth[Amount] ) > 0PastAverage )

Untitled

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.

Capture.JPG

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] ) > 0PastAverage )

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.

Capture

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] ) > 0PrevYTD )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

%d bloggers like this: