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.

  1. Calendars and Dates in Analysis Services Tabular Models (this blog)
  2. Creating a calendar table for use in SSAS Tabular
  3. Using timelines in pivot tables

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

Examples of date aggregator 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:

Summarising by period

Here we're showing total sales by year and month.

 

This would give you the required results:

Results by year and month

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:

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:

Calendar table relationship

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:

Quantity summed by month and year

Again we're showing total sales by month and year.

 

This then allows us to aggregate data by (for example) month and year:

Pivot table

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:

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

  1. Calendars and Dates in Analysis Services Tabular Models (this blog)
  2. Creating a calendar table for use in SSAS Tabular
  3. Using timelines in pivot tables
This blog has 0 threads Add post