564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to use DAX date-based functions in Analysis Services tabular model
Part three 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.
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.
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:
And there's not much more to say about this!
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:
|What it means
|The calendar dates corresponding to the pivot table query context.
|How many periods forward (if positive) or back (if negative) you want to go in time.
|Which period you want to use (MONTH, QUARTER or YEAR).
Here's what our measure could look like:
Last year using DATEADD:=CALCULATE(
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.
|Parts of this blog
25 Aytoun Street