560 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
Ways to model different DAX calendar date situations
Part three of a six-part series of blogs
In the real world (or at least, the DAX version of it), calendars and dates aren't quite as simple as one would like. This blog shows how to solve a number of common scenarios, including where different tables have different levels of "granularity", coping with special days like bank holidays, handling non-standard finanical year-end dates and dealing with a table which contains more than one date column.
This blog explains how to show for each calendar date whether it is:
You can apply similar principles to categorise dates in any other way that you choose.
There's no getting around it - to get this to work you're going to have to beg, borrow or type in a table of bank holidays for those years in which you could conceivably have an interest:
For this example, you can download the Excel workbook of bank holidays for 2013/2014 for the UK.
Once you've got your hands on a workbook, table or CSV file like this, you should import it into your model:
When importing the data (I did it via SQL Server), I've given it the friendly table name BankHoliday.
You should now have another table in your model:
The imported table of bank holidays.
You should now link the calendar and bank holiday table - but carefully:
You should drag the calendar Date column onto the bank holiday BH_Date one.
Normally it doesn't matter which way round you drag, because SSAS Tabular can work out the nature of the relationship from the data in each of the two tables. Here it can't, so you need to be careful. The way to think of it is that for each date you are looking it up in the bank holiday table to see if it exists.
To find out if a day is a bank holiday or not, you can use the IF and RELATED functions introduced earlier in this series to answer this question:
A formula that you could use is shown below:
This formula would determine whether a particular date is a bank holiday or not.
You could actually abbreviate this to:
This proposes the statement that there is a corresponding row in the bank holiday table for the current calendar date, which is either true (in which case it is a bank holiday) or false (in which case it isn't).
Finally, we need to see if a date is either a bank holiday or a Saturday/Sunday (we're using UK weekends here). To do this you could use the WEEKDAY function, which has the following return types:
|Return type||Week definition|
|1||Week runs from Sunday (1) through to Saturday (7)|
|2||Week runs from Monday (1) through to Sunday (7)|
|3||Week runs from Monday (0) through to Sunday (6)|
We'll use the return type of 2, to get the formula for the IsWorkingDay function shown below:
The formula to use, explained below. New Year's Day in 2013 was on a Tuesday.
The formula uses the || double-pipe symbol (look at the bottom left of your keyboard, next to the SHIFT key) to denote OR. Here's the expression in full:
WEEKDAY([Date],2) > 5,
Thus if the data is a bank holiday or it's on a 6 (Saturday) or 7 (Sunday), it isn't a working day; otherwise it is.
The main reason to create a new way of categorising a date is so that you can create pivot tables like this:
Total sales were higher on working days!
For this particular pivot table, you'd have to tweak the calculated column to show text strings rather than just true/false:
WEEKDAY([Date],2) > 5,
Next in this blog I'll show how to model the similar situation where your financial year doesn't end nicely on 31st December.
|Parts of this blog|
25 Aytoun Street