Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
575 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
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.
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.
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:
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.
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.
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.
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.
You can import your calendar like any other table:
Choose to import the calendar table like any other.
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.
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.
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.
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).
Timelines work in almost exactly the same way as slicers, but they allow you to choose dates, rather than text or numbers. Here's an example:
Showing only transactions made in July to September 2014.
Timelines were introduced in Excel 2013, so you'll search in vain for them in Excel 2010.
A pre-requisite for adding a timeline (at least, for a pivot table based on an SSAS Tabular model) is that you should have a calendar table. Assuming you have this, choose to insert a timeline:
Choose this option on the Analyze tab of the ribbon to insert a timeline.
Now tick the box to say which table contains your dates:
For most people, there will only ever be one table they have designated as a date table.
You can now specify in what units your timeline will work:
Click on the arrow at the top right to choose your units.
Click and drag with the arrow shown to change the period of time for the pivot table:
Click and drag at either end of the timeline with the arrow which appears (as shown here) to shorten or lengthen the period covered.
One thing, however, that you can't do is to hold down the CTRL key to select date ranges which are non-consecutive.
As for a slicer, you can specify a style for a timeline (and even create your own styles):
Click on the drop arrow show to choose from an even wider range of styles than those appearing on the screen by default.
You can tick or untick boxes on the Options tab of the ribbon to control what appears on your timeline:
The options available.
Here's a before-and-after of what you can show/hide:
With everything ticked | Only the scroll bar ticked |
When you've had enough of your timeline, you can reset it:
Click here to remove any filtering imposed by the timeline, and choose all possible dates.
Alternatively, you could just delete the timeline completely:
Right-click and choose this option to get rid of your timeline altogether.
You can right-click on a timeline and choose this menu option to control what pivot tables it filters:
This works in exactly the same way as it does for slicers.
You can then tick the pivot tables which this timeline should control:
It would have been a good idea to give these pivot tables better names!
And with that, I think I've exhausted the subject of timelines!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.