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 four 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 (this blog) Moving averages using DAX date functions 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 four 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 (this blog)
Moving averages using DAX date functions
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

Using PARALLELPERIOD to get the whole of the last period

The PARALLELPERIOD function returns a set of dates shifted forwards or backwards in time. The easiest way to understand this sentence is by example!

The shaded figures show cumulative sales to date for 2014 (1,490) as a percentage of total sales for the whole of 2013 (9,470).

Here's the formula for the PercentLastYear measure:

PercentLastYear2:=TOTALYTD(

sum('Transaction'[Quantity]),

Calendar[Date]

) /

CALCULATE(

sum('Transaction'[Quantity]),

PARALLELPERIOD(

Calendar[Date],

-1,

YEAR)

)

That is, divide total year-to-date quantity sold by the total quantity sold one year ago.

Avoiding returning an error

The measure above shows a divide-by-zero error for 2013 figures, because the previous year's data doesn't exist. One way to get round this would be to use an IFERROR function to trap the error, and return blank:

PercentLastYear:=IFERROR(

TOTALYTD(

sum('Transaction'[Quantity]),

Calendar[Date]

) /

CALCULATE(

sum('Transaction'[Quantity]),

PARALLELPERIOD(

Calendar[Date],

-1,

YEAR

)

BLANK()

)

That is, if dividing the year-to-date total sales by the previous year's total sales returns an error, show a blank instead. This would give:

Much better!

Having gone through 3 relatively straightforward tasks, let's now look at something a bit more challenging: displaying moving averages.

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 (this blog) Moving averages using DAX date functions 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

Using PARALLELPERIOD to get the whole of the last period

Avoiding returning an error

Head office

London

Manchester