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 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.
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.
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.
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|
25 Aytoun Street