BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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 (this blog)
- Using PARALLELPERIOD to get the whole of the last period
- 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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# The same period in a previous month, quarter or year

It's often useful to compare a figure with the equivalent figure in a
previous year. This page shows two ways to do this, using either the
**SAMEPERIODLASTYEAR** function or the **DATEADD** function.

For example, the figure for January 2014 shows up as 733, since it's picking up the figure for the same time period 12 months earlier.

## Using the SAMEPERIODLASTYEAR function

If ever a function was well-named, it's this one! Here's the syntax:

The function gives the set of dates for the same period, but 12 months earlier.

So a measure which would achieve the results shown above could be:

Last year:=CALCULATE(

SUM('Transaction'[Quantity]),

SAMEPERIODLASTYEAR(Calendar[Date])

)

And there's not much more to say about this!

## Using the DATEADD function

I must admit I prefer this solution, since it's more generally applicable,
the function name is shorter and I think it's easier to understand. The
format of the **DATEADD** function is as follows:

Add a specific number of intervals of the required type to the current set of dates.

The arguments are:

Argument | What it means |
---|---|

Dates |
The calendar dates corresponding to the pivot table query context. |

NumberOfIntervals |
How many periods forward (if positive) or back (if negative) you want to go in time. |

Interval |
Which period you want to use (MONTH, QUARTER
or YEAR). |

Here's what our measure could look like:

Last year using DATEADD:=CALCULATE(

sum('Transaction'[Quantity]),

DATEADD(

Calendar[Date],

-1,

YEAR

)

)

It takes quite a bit of confidence to type the **YEAR**
interval, since SSAS keeps trying to autocorrect it to the **YEAR()**
function.

This measure should give the same result as the previous one:

The two measures compared (the results are the same).

Time now to look at a function which shows the value of a measure for the
whole of a previous period: **PARALLELPERIOD**.

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