This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Modelling techniques when working with calendars/dates Dealing with varying date granularity using calendars Modelling bank holidays and other special days (this blog) Modelling different financial year-ends Handling multiple date columns using multiple tables 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.

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.

Modelling techniques when working with calendars/dates
Dealing with varying date granularity using calendars
Modelling bank holidays and other special days (this blog)
Modelling different financial year-ends
Handling multiple date columns using multiple tables
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

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:

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.

Creating a relationship to the bank holidays table

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.

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:

]

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 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:

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.

Parts of this blog
Modelling techniques when working with calendars/dates Dealing with varying date granularity using calendars Modelling bank holidays and other special days (this blog) Modelling different financial year-ends Handling multiple date columns using multiple tables Handling multiple date fields using multiple relationships

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR