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 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.
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!
|Parts of this blog|
25 Aytoun Street