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

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

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:

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:

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:

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:

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:

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?

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

Drag the birth date column onto the calendar date column.

Now go back into your 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:

I had the foresight to rename mine as MAM.

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

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:

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:

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

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 Modelling different financial year-ends Handling multiple date columns using multiple tables (this blog) 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

Handling multiple date columns using multiple tables

Producing the data model for this case study

One solution - import the calendar table more than once

Head office

London

Manchester