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.

  1. Modelling techniques when working with calendars/dates
  2. Dealing with varying date granularity using calendars
  3. Modelling bank holidays and other special days (this blog)
  4. Modelling different financial year-ends
  5. Handling multiple date columns using multiple tables
  6. Handling multiple date fields using multiple relationships

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 22 January 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.

Modelling bank holidays and other special days

This blog explains how to show for each calendar date whether it is:

  • A bank holiday; and/or
  • A working day

You can apply similar principles to categorise dates in any other way that you choose.

Creating and importing a table of bank holidays

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:

Bank holidays list

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:

Importing bank holidays

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:

Imported table of bank holidays

The imported table of bank holidays.

 

Creating a relationship to the bank holidays table

You should now link the calendar and bank holiday table - but carefully:

Drag to create relationship

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.

Determining whether a day is a bank holiday or not

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:

"If I look up this date in the bank holiday table, will it create a blank?"

A formula that you could use is shown below:

]Function determining if bank holiday

This formula would determine whether a particular date is a bank holiday or not.

You could actually abbreviate this to:

=(RELATED(BankHoliday[BH_Date])<>BLANK())

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).

Determining whether a date is a working date

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 for working days

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:

=IF(

[IfBankHoliday] ||

WEEKDAY([Date],2) > 5,

false,

true

)

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.

Using the calculated column in a pivot table

The main reason to create a new way of categorising a date is so that you can create pivot tables like this:

Pivot table for working days

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:

=if(

[IfBankHoliday] ||

WEEKDAY([Date],2) > 5,

"Lazy day",

"Working day"

)

 

Next in this blog I'll show how to model the similar situation where your financial year doesn't end nicely on 31st December. 

This blog has 0 threads Add post