556 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
How to create a calendar in SSAS Tabular to summarise by dates
Part one of a three-part series of blogs
Analysis Services has lots of wonderful DAX functions like TOTALYTD and SAMEPERIODLASTYEAR which allow you to summarise your results by date, but to get them to work you'll need to create and use a calendar table first. This blog explains how, and why.
When I first learned how to work with calendars and dates in SSAS Tabular, I was a bit taken aback.
In case you are too, I thought I'd start by showing the way I expected things to work, and the way they actually do work.
In all other software I've used, the way to aggregate by date would be to use formulae to create additional columns:
Some examples of date aggregator columns, to show the year, month name and number for any given date.
You could then use these to summarise results by month, year or whatever:
Here we're showing total sales by year and month.
This would give you the required results:
OK, the months need sorting correctly, but otherwise this is useful information.
However, this isn't the way you're meant to summarise by date in SSAS Tabular.
One of the problems with the above approach is that you'd have to recreate the date aggregator columns in other tables.
It turns out that the way to proceed in SSAS Tabular is to create a single calendar table:
The first few dates in a calendar table running from 2013 onwards.
This should contain:
You should then create a relationship between the calendar table and any other table containing dates:
We can look up each point-of-sale date in the calendar table, and find in which day, month, quarter and year it occurred.
You can then use these calendar columns to summarise data. For example:
Again we're showing total sales by month and year.
This then allows us to aggregate data by (for example) month and year:
Again we have a bit of an issue with sorting the months - see later in this blog for the best way to solve this.
Creating a separate calendar table has two overwhelming advantages:
|Single storage||If you come up with a new way to aggregate data, you can just add a new column into the calendar table.|
|Compatibility||Many DAX functions assume that you have a separate dates table, and that all the dates in it are consecutive (with no gaps).|
Initially it seemed a bit wasteful to store one row in a table for every date, but even 100 years would only give 36,500 rows, which is peanuts for a modern computer.
Now I have (I hope) eliminated any mental block that you may have to the approach used by SSAS Tabular to dates, it's time to create our calendar table.
|Parts of this blog|
25 Aytoun Street