DAX – Slicers For Selecting Last “X” Periods

Guest post by David Churchward

Using Time Intelligence in PowerPivot can appear scary when you first start using it and I’ve seen some weird and wonderful ways of attacking it, some that look like we’re calling into question the validity of the global phenomenon that is time and others that are quite simply brilliant.

In this post, I hope to provide a simple and understandable approach whilst adding another flexible dimension called “X” where “X” is a variable that can be set by a user in a slicer.  This might be, for example, a P&L report where the user wants to see the last 4 months instead of the normal pre-determined timeframes such as year to date or quarters.  Alternatively, the report may be reviewing trends and, again, year to date or one of the other standard timeframes may not be the most relevant timeframe.

Incidentally – if you’re in the mood for conquering time intelligence there’s a host of brilliant information on this site including posts such as Running Totals Without a Traditional Calendar Table and PowerPivot time intelligent functions: why use ALL() and how to work around it which give some excellent direction on Time Intelligence and some of the complexities.

In this post, I’ll explain how you can put all of the control in the hands of the user by creating an X Periods measure. That is to say that the user can review the last X periods up to a date that they control.

Paving the Way

The crux of dealing with time intelligence in this way is to create a time table that links to your dataset and then a further time selection set of tables from which the user can select their required time parameters.

I’ve created so many of these where the fiscal year isn’t a conventional calendar year that I’ve found that it’s best to deal with the fact that some of the built-in time intelligence PowerPivot functions may not be valid. However, with last X periods, it doesn’t matter anyway.

I need the following tables in my dataset:

FACT_Tran– this is my fact table dataset that I wish to analyse.

Dates– this is an unbroken list of dates representing the timeframe of my dataset. Time Intelligence works better when your fact table is linked to a dates table, chronologically ordered without any breaks.

Year– a list of years in the dataset

Period – a list of periods in the dataset. This would normally be 1 to 12

Year_Period– this is more aligned to a proper time dimension in that it is a combination of the years and periods representing my dataset and carries additional information such as month end dates that we need for our measures (this version has been simplified for the purposes of this post)

X– this is a single column table that allows users to select the number of periods that they wish to see. This could be a numeric sequential number or specific timeframes that are most likely to be selected as in my example here.

Note – I’m using a predetermined set of values for X which contradicts my opening remarks but this is purely for the purposes of showing this example and it really depends on the situation!

Links

You’ll notice that my Year_Period table is linked to my Year and Period tables. My fact table is separately linked to my Dates table. For this method of Time Intelligence to work, you shouldn’t link the two sets of tables together otherwise filtering will occur when we don’t want it to.  You can get around this, but there’s no need to link the two sets of tables anyway.

Determining what has been Selected

Users will be given slicers to select the year and period that they are working on. In terms of X periods, we would consider this to drive the end date of the period to which X relates.  We then work back X periods from that date.

I need to create a series of measures to determine what the user has selected. These are as follows:

Selected_Month_End_Date = LASTDATE(Year_Period[Month_End_Date])

Selected_X = MAX(X[X])

Selected_X_Months_Start_Date = LASTDATE(DATEADD(Year_Period[Next_Month_Start_Date],[Selected_X]*-1,MONTH))

As I’m using Fiscal Periods and my financial year starts in July, period 3 for the year ending 2010 (as selected above) is actually September 2009. I’ve selected 9 as a value for X which means that I’m looking back 9 months from September 2009. My start date is therefore 1st Jan 2009.

Building These Dates into my Financial Measure

Now that we know what the user has selected in terms of X and the date range to which X relates, our financial measure is now very simple.

X_Periods_Value

=CALCULATE

(

SUM(FACT_Tran[Value]),

DATESBETWEEN(Dates[Date],[Selected_X_Months_Start_Date],[Selected_Month_End_Date])

)

We now have a dynamic measure which calculates the start and end dates that we wish to see and applies those dates to our value column.

This can be massaged to calculate year to date, prior year and many other date values as required and it is totally dynamic without any back end calculations.

Taking Things to the Next Level

The method above is what I use regularly to allow for year, period and X selectors to drive the analysis.  This works a treat if your primary reporting timeframe is a month.  However, what if days or weeks are a relevant timeframe?

Let’s adapt what we’ve already done by creating a new dates table.  Let’s call this table Dates2.  This is a sequential date column similar (if not identical) to Dates but NOT linked to our fact table.  Create 3 new measures on this table which are variations of the Selected_X_Months_Start_Date measure that we created previously.

Selected_X_Days_Start_Date

= DATEADD(LASTDATE(DATEADD(Dates2[Date],[Selected_X]*-1,DAY)),1,DAY)

Selected_X_Weeks_Start_Date

= DATEADD(LASTDATE(DATEADD(Dates2[Date],[Selected_X]*-7,DAY)),1,DAY)

Selected_X_Months_Start_Date

= DATEADD(LASTDATE(DATEADD(Dates2[Date],[Selected_X]*-1,MONTH)),1,DAY)

So why do we need two DATEADD functions in each of these measures? Simply put, we’re essentially using dates here. That’s a simple statement that doesn’t answer anything! What I mean is that we’re essentially not using datetime fields. Each date is therefore a value in it’s own right. Therefore, if we treat 30th September as a day only and therefore a value of 30, when we subtract one day, we would get 29. As a result, we would be reviewing 29th and 30th when in reality we only want to review one day. Since 30th is one day in it’s own right, we always have to add one day to each answer.

Note – take care to ensure that your fact table dates are in the same format.  Time can skew the answer if you’re not careful.

In the previous solution using year and period slicers to select months above, this problem was dealt with by using the Next_Month_Start_Date value which is one day ahead of our month end date selected and held as a value in our Year_Period table.

Our weeks solution is a derivative of the days function where we simply adjust by days * 7 to get to weeks.

I now create another selection table called Time_Type.  This is designed to select which timeframe type the user is selecting.

I create a slicer on my pivot table using the Type_Name and create a measure which determines which item has been selected by the user.

Selected_TimeType

= MAX(TimeType[Type_Code])

I then create another measure which uses the selected time type to determine which of the 3 measures to use:

Selected_X_TimeType_Start_Date

= IF([Selected_TimeType]=1,[Selected_X_Days_Start_Date],

IF([Selected_TimeType]=2,[Selected_X_Weeks_Start_Date],

IF([Selected_TimeType]=3,[Selected_X_Months_Start_Date],

   BLANK()

  )

   )

     )

We need one further measure to determine which date has been selected

Selected_Date2

= LASTDATE(Dates2[Date])

These new measures can now be used in our value measure:

X_Periods_Value

=CALCULATE

(

SUM(FACT_Tran[Value]),

DATESBETWEEN(Dates[Date],[Selected_X_TimeType_Start_Date],[Selected_Date2])

)

What if I Always Want my Report to be X Periods to Today’s Date?

This is simple.  We simply need to substitute Selected_Date2 for a new measure called Todays_Date in our X_Periods_Value measure and substitute Dates2[Date] with Todays_Date in Selected_X_Days_Start_Date and Selected_X_Weeks_Start_Date and Selected_X_Months_Start_Date measures

Todays_Date

= TODAY()

You do however need to take care about what your data content is.  As an example, I use a data warehouse that is built every night.  When I refer to today, I actually mean last night which means that my measure should in fact be

Todays_Date_LastNight

= TODAY() -1

What Does This Mean?

By using this method, we have passed the requirement to determine the timeframe to which a trend or report relates across to the user.  Trend timeframes could change and do we really want to have to recreate reports and dashboards as a result?  I hope that this solution solves that problem.

Having learnt from my previous post, YOU CAN DOWNLOAD THE WORKBOOK HERE.