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:
|Date||Dynamic day||Dynamic month||Dynamic quarter||Dynamic year|
|04-12-2012||Current day -202||Current month -6||Current quarter -2||Current year -1|
|11-03-2013||Current day -105||Current month -3||Current quarter -1||Current year|
|28-04-2013||Current day -57||Current month -2||Current quarter||Current year|
|23-06-2013||Current day -1||Current month||Current quarter||Current year|
|24-06-2013||Current day||Current month||Current quarter||Current year|
|25-06-2013||Current day +1||Current month||Current quarter||Current year|
|01-07-2013||Current day +7||Current month +1||Current quarter +1||Current year|
|15-08-2013||Current day +52||Current month +2||Current quarter +1||Current year|
|23-11-2013||Current day +152||Current month +5||Current quarter +2||Current year|
|14-02-2014||Current day +235||Current month +8||Current quarter +3||Current year +1|
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.
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.
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.