This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Calendars and Dates in Analysis Services Tabular Models Creating a calendar table for use in SSAS Tabular (this blog) 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.

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.

Calendars and Dates in Analysis Services Tabular Models
Creating a calendar table for use in SSAS Tabular (this blog)
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

Creating a calendar table for use in SSAS Tabular

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

Creating a calendar table in SQL Server (or Excel).
Importing this into your model.
Telling SSAS Tabular which is your date table (!).
Specifying how months should be sorted.
Creating relationships between your calendar and other date tables.
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.

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.

Step 2 - Importing the calendar table into SSAS Tabular

You can import your calendar like any other 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:

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!

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:

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:

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:

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:

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:

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:

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).

Parts of this blog
Calendars and Dates in Analysis Services Tabular Models Creating a calendar table for use in SSAS Tabular (this blog) Using timelines in pivot tables

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR