This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

How to use DAX date-based functions in Analysis Services tabular model Part five of a six-part series of blogs
There are some wonderful date functions in DAX, with evocative names like TotalYtd, SamePeriodLastYear and ParallelPeriod. This blog shows how to use DAX date functions to summarise data, assuming that you have already created a calendar table. Creating date-based functions in DAX in SSAS Tabular Cumulative totals (TotalYTD, DatesYTD, etc) The same period in a previous month, quarter or year Using PARALLELPERIOD to get the whole of the last period Moving averages using DAX date functions (this blog) Semi-additive measures in DAX This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

How to use DAX date-based functions in Analysis Services tabular model

Part five of a six-part series of blogs

There are some wonderful date functions in DAX, with evocative names like TotalYtd, SamePeriodLastYear and ParallelPeriod. This blog shows how to use DAX date functions to summarise data, assuming that you have already created a calendar table.

Creating date-based functions in DAX in SSAS Tabular
Cumulative totals (TotalYTD, DatesYTD, etc)
The same period in a previous month, quarter or year
Using PARALLELPERIOD to get the whole of the last period
Moving averages using DAX date functions (this blog)
Semi-additive measures in DAX

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 08 February 2016

Moving averages using DAX date functions

There is no moving average function in DAX, so this isn't going to be straightforward! Here's what we'll produce:

For February 2014, for example (shown shaded), the monthly moving average is 794 (that is, 9,528, the quantity sold for March 2013 through to February 2014, divided by 12).

Two new functions introduced

The moving average formula uses the NEXTDAY function:

Given a set of dates, this function returns the date immediately following them.

Our moving average formula also uses the LASTDATE function:

Given a set of dates, this function returns the last date amongst them.

The moving average formula

Our formula could look like this:

Monthly moving average:=CALCULATE(

SUM('Transaction'[Quantity]),

DATESBETWEEN(

Calendar[Date],

NEXTDAY(

SAMEPERIODLASTYEAR(

LASTDATE(Calendar[Date])

)

LASTDATE(Calendar[Date])

)

)/12

To illustrate what this is doing, let's see what it does for the shaded cell (February 2014), breaking the function down into parts:

Part	What it returns
LASTDATE(Calendar[Date])	For the corresponding dates (those in February 2014) this returns the last date among them (ie 28th February 2014).
NEXTDAY( SAMEPERIODLASTYEAR (LASTDATE (Calendar[Date]) ))	For the dates for the same period in the previous year (ie the last day of February in 2013), find the following day (ie 1st March 2013).
DATESBETWEEN( Calendar[Date], NEXTDAY( SAMEPERIODLASTYEAR ( LASTDATE (Calendar[Date]) )) , LASTDATE( Calendar[Date] ))	For February 2014, for example this will return the set of dates between 1st March 2013 and 28th February 2014.

Simples!

Dividing by the correct number

The above formula contains a fudge: we've assumed that we'll always divide by 12 (for months), but this will give the wrong figures for early months (when we don't yet have 12 months of data to go on). To get round this, you could create the following formula for the number of periods:

Number of periods:=CALCULATE (

CALCULATE (

COUNTROWS (

VALUES ( Calendar[MonthName] )

)

DATESBETWEEN (

Calendar[Date],

NEXTDAY ( SAMEPERIODLASTYEAR (LASTDATE ( Calendar[Date] ) ) ),

LASTDATE ( Calendar[Date] )

)

Here's how this works. The second part of the formula returns (for February 2014) the period between 1st March 2013 and 28th February 2014, as we've seen:

This returns a period of up to 12 months.

The first bit of the formula then counts how many rows there are in the set of months contained within this range, to get:

The formula cleverly gives the right number of months even at the start of the first year.

Credit where credit's due: I adapted the idea from the divisor from this excellent article on moving averages.

Getting the final correct moving average

Finally, you can divide total sales by this divisor to get the final moving average:

Monthly moving average:=CALCULATE(

SUM('Transaction'[Quantity]),

DATESBETWEEN(

Calendar[Date],

NEXTDAY(

SAMEPERIODLASTYEAR(

LASTDATE(Calendar[Date])

)

LASTDATE(Calendar[Date])

)

) / [Number of periods]

This gives the same figures, but will do so correctly even at the start of the first year.

For the final part of this blog we'll look at the grandiose-sounding semi-additive measures.

Parts of this blog
Creating date-based functions in DAX in SSAS Tabular Cumulative totals (TotalYTD, DatesYTD, etc) The same period in a previous month, quarter or year Using PARALLELPERIOD to get the whole of the last period Moving averages using DAX date functions (this blog) Semi-additive measures in DAX

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Moving averages using DAX date functions

Two new functions introduced

The moving average formula

Dividing by the correct number

Getting the final correct moving average

Head office

London

Manchester