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

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

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:

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.

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