How to create a calendar in SSAS Tabular to summarise by dates
Part two 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
  2. Creating a calendar table for use in SSAS Tabular (this blog)
  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.

Creating a calendar table for use in SSAS Tabular

This blog page shows the following steps (under separate headings below):

  1. Creating a calendar table in SQL Server (or Excel).
  2. Importing this into your model.
  3. Telling SSAS Tabular which is your date table (!).
  4. Specifying how months should be sorted.
  5. Creating relationships between your calendar and other date tables.
  6. Use your calendar fields in a pivot table.

Let's start, then, with creating a calendar table, which you can then import into SSAS Tabular.

Step 1 - Creating a calendar table

The easiest way to create a calendar table in SQL Server is to download, customise and run a stored procedure like this one.  More information if necessary is given in this blog.

The calendar table produced

Running the stored procedure should generate a table like this in the MAM database. You need to know a bit about SQL to accomplish this, however.

 

Alternatively, you could follow the instructions in this blog to create your calendar in Excel (the instructions are for PowerPivot, but would be the same for SSAS Tabular).

Step 2 - Importing the calendar table into SSAS Tabular

You can import your calendar like any other table:

Importing calendar table

Choose to import the calendar table like any other.

Step 3 - Identifying that you have a Date Table

Once you've imported your calendar table, select the table and then select the following menu option:

Mark as date table

You can do this in either data or diagram view.

SSAS Tabular then suffers from a common-sense deficit.  You have to tell it which of your fields uniquely identifies each date, even though it should be fairly obvious!

Date field identification

Confirm that the Date column (at least in this table) contains unique dates for each row.

Step 4 - Sorting Months

It's a good idea to get out of the way now how client tools (ie pivot tables) will sort months.  To do this select the MonthName column, for example:

The MonthName column

You could repeat this trick for any column whose default sorting you want to alter.

 

Press F4 if necessary to bring up the Properties window, then specify which column should be used for sorting when this field is included in a pivot table:

Sort by column

Choosing the MonthNumber column will ensure that months are sorted January to December, rather than alphabetically (which would have been the default). 

 

If you'd rather start with a different month, just create a new column in your calendar table, set the values for each date and sort by that instead.

Step 5 - creating relationships for your calendar table

You won't be able to aggregate by date fields until you join your calendar table to the world:

Calendar join

Click and drag the PosDate field from the Pos table onto the Date field for the Calendar table, to look up the calendar date for each point-of-sale record.

 

Step 6 - Using calendar fields in pivot tables

As for all other tables, you should hide from client tools any fields you think you'll be unlikely to want to aggregate by:

Hiding calendar fields

Here we assume we'll only want to aggregate by month, quarter or year.

 

As ever, you'll be able to choose which fields you want to aggregate by for your pivot table:

Choosing pivot table fields

The fields which you haven't hidden from client tools will appear in the list. Here we're summing by year, quarter and month.

 

Here's the sort of pivot table this can produce:

Pivot table for a calendar

You can see summaries by year, quarter and month.

 

It's sometimes a good idea to set up a hierarchy, so that you can add the year/quarter/month combination in one go.  Hierarchies will be covered in a later blog in this series.

Finally for this blog, I'll show how you can create and use timelines for a pivot table to filter by date (similar to slicers, but requiring a calendar table to work).

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