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

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.

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

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:

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:

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.

Customising timelines

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.

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.

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


With everything ticked	Only the scroll bar ticked

Clearing and removing timelines

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.

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:

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!

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

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