564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
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.
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).
|Parts of this blog|
25 Aytoun Street