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 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.
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:
That is, divide total year-to-date quantity sold by the total quantity sold one year ago.
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:
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:
Having gone through 3 relatively straightforward tasks, let's now look at something a bit more challenging: displaying moving averages.
|Parts of this blog|
25 Aytoun Street