Dynamic periods in SSAS Tabular

Use case

Often, users of BI solutions find themselves needing a dynamic approach towards filtering on dates, like always use current month, next 30 days, past 12 months and so on. I call this dynamic periods, though closely related to rolling periods.

This is usually solved by creating different measures for each use case, or by using a shell dimension using the MDX SCOPE command as illustrated in Using the Time Intelligence feature of SSAS Multidimensional as illustrated, and Rolling period with shell dimension in SSAS.

However, SSAS Tabular does not offer a Time Intelligence feature, nor does DAX  offer an equivalent to the MDX SCOPE. Instead of trying to adapt different techniques for SSAS Multidimensional, I wished to take advantage of one of the greatest benefits in Tabular; It does not rely on aggregations.

How does it work

A different approach is to simply add new attributes to the data dimension for each dynamic period, like Dynamic Day, Dynamic Month, Dynamic Quarter and Dynamic Year.

This method is highly suitable for SSAS Tabular solutions as well as for smaller data warehouses in SSAS Multidimensional, since it requires the date dimension to be processed daily. Remember, a ProcessUpdate in SSAS Multidimensional requires a ProcessIndex afterwards, which may take a significant amount of time. In a multidimensional model, additional aggregations may also be necessary.

This approach has the advantage of working with all measures, and in all clients. It will even work with adhoc reporting using SQL. And, it is extremely easy and fast to implement. Furthermore, the user is not restricted to any predefined periods. It could easily be implemented in a fiscal calendar or any other custom calendar.

How to build it

Simply add the new attributes to your date dimension for each rolling period. If today is June 24th 2013, a few example values would be:

DateDynamic dayDynamic monthDynamic quarterDynamic year
04-12-2012Current day -202Current month -6Current quarter -2Current year -1
11-03-2013Current day -105Current month -3Current quarter -1Current year
28-04-2013Current day -57Current month -2Current quarterCurrent year
23-06-2013Current day -1Current monthCurrent quarterCurrent year
24-06-2013Current dayCurrent monthCurrent quarterCurrent year
25-06-2013Current day +1Current monthCurrent quarterCurrent year
01-07-2013Current day +7Current month +1Current quarter +1Current year
15-08-2013Current day +52Current month +2Current quarter +1Current year
23-11-2013Current day +152Current month +5Current quarter +2Current year
14-02-2014Current day +235Current month +8Current quarter +3Current year +1

Example

In the example below from Adventure Works, shows the measure ‘Days In Current Quarter’ distributed by year. The analysis makes little sense, but it was the only measure in AdventureWorks with a large enough distribution on the Date dimension. The pivot table shows data for the years 2005-2014.

DynamicYear example

When placing a filter on Dynamic Year = Current year the report now only shows data for 2013. Remember, earlier we defined today as June 24th 2013. When the analysis is used at a later point in time, it will automatically adjust to the current year.

DynamicYear example2

Disclamer

Unfortunately, I have not had the chance to actually try this on a production system. If you do, I would love to hear about your experiences.