How to create a calendar in SSAS Tabular to summarise by dates
Part three 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
  3. Using timelines in pivot tables (this blog)

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.

Using timelines in pivot tables

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:

Example of timeline

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.

Adding a timeline

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:

Insert 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:

Insert timeline calendar

For most people, there will only ever be one table they have designated as a date table.

 

Customising timelines

You can now specify in what units your timeline will work:

Choosing timeline units

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 time period

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

Timeline styles

Click on the drop arrow show to choose from an even wider range of styles than those appearing on the screen by default.

Controlling what appears on a timeline

You can tick or untick boxes on the Options tab of the ribbon to control what appears on your timeline:

The options available

The options available.

 

Here's a before-and-after of what you can show/hide:

With everything ticked Only the scroll bar ticked
With everything ticked Only the scroll bar ticked

Clearing and removing timelines

When you've had enough of your timeline, you can reset it:

Clearing timeline filter

Click here to remove any filtering imposed by the timeline, and choose all possible dates.

Alternatively, you could just delete the timeline completely:

Removing the timeline

Right-click and choose this option to get rid of your timeline altogether.

Getting a timeline to control multiple pivot tables

You can right-click on a timeline and choose this menu option to control what pivot tables it filters:

Report connections menu

This works in exactly the same way as it does for slicers.

You can then tick the pivot tables which this timeline should control:

Choosing pivot tables

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!

This blog has 0 threads Add post