BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Calendars and Dates in Analysis Services Tabular Models (this blog)
- Creating a calendar table for use in SSAS Tabular
- Using timelines in pivot tables
Posted by Andy Brown on 21 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.
Calendars and Dates in Analysis Services Tabular Models
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.
The (wrong) way I thought things would 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.
The correct way - create a date table
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:
- Every date that could be of conceivable interest; and
- Every way of aggregating dates that could be useful (including month, quarter, financial year, etc.).
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.
The reasons for using a separate calendar table
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.