Ways to model different DAX calendar date situations
Part five 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
  4. Modelling different financial year-ends
  5. Handling multiple date columns using multiple tables (this blog)
  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.

Handling multiple date columns using multiple tables

A common situation in SSAS Tabular is that you only have one calendar table, but you want to use it on multiple occasions:

Joining on two dates

Suppose you want to summarise staff by when they were born and when they joined, using the two date fields shown selected. How can you do this?

The output we're trying to produce is this:

Summary by birth and joining year

For example, there are 4 members of staff who were born in 1995 and who joined the MAM company in 2011.

I'll first show how to set up this model, then how to solve this problem.

Producing the data model for this case study

If you want to follow along with this case study, you'll need to make sure that your calendar contains dates going back to the earliest year when someone might have been born.  To do this, follow these instructions to create a calendar table, but change the start date to (say) 1st January 1950:

Calendar table start date

Change the stored procedure to start much earlier, recreate it and re-run it, as described here.

Now create a new data model using the MAM database and import these two tables:

Staff and calendar tables

Import the tblStaff and tblCalendar tables, and give them friendly names as shown here.

In the staff table, create a measure to count how many staff there are for each query context:

Counting staff

A measure to count the number of staff for any pivot table cell.

The problem comes now: which of the two date fields do you join to the calendar date column?

Two date fields

You can't join by both dates, otherwise you won't get any data in your pivot table.

One solution - import the calendar table more than once

The best solution, I think (I'll show another one using multiple relationships in the final part of this blog) is to import your calendar table multiple times.  First rename the one you have:

Rename calendar table

Rename the calendar table you've imported (let's say it will link to the birth date, so we'll call it BirthCalendar).


Now create a relationship between the BirthDate column and the BirthCalendar table:

Birth date relationship

Drag the birth date column onto the calendar date column.

Now go back into your existing connections:

Existing connections

The menu option to view your model's connections (there's a tool to do the same thing on the SSAS toolbar).


Double-click on the connection you created to import your tables:

Choose a connection

I had the foresight to rename mine as MAM.

Choose to import the calendar table again, but this time call it JoinCalendar:

The join calendar table

Here we've used the friendly name JoinCalendar.

Drag the DateJoined field from the Staff table onto the Date field in the JoinCalendar table to create a second relationship:

Join date relationship

Creating the relationship to find the year in which people joined.

You can then create the pivot table shown at the start of this blog:

Two year columns

It's not obvious that the Year on the left is the birth year and the Year at the top the joining year, so it might be an idea to go back and give your calendar columns less ambiguous names.


The final result:

The final pivot table

The only tweak I've done is to change the sort order for the two years.

Purists may not like this approach, since it involves storing the rows in the calendar table multiple times (once for each date).  However, it's easy to understand and use, and memory is rarely that much of a constraint. 


For those who don't like the concept of importing multiple calendar tables, try the final part of this blog, which shows how to create multiple relationships instead. 

This blog has 0 threads Add post