Building Relative Date Reports in PowerPivot

It’s a very common requirement when you’re building a report in PowerPivot (or indeed in any BI tool) for it to automatically show data for today’s date, the current week or month (plus maybe a set number of preceding days, weeks or months), without the user needing to change anything when they open the workbook. There are a number of ways of achieving this, but in this post I’m going to focus on one: building relative date columns in your Date dimension table. This stuff is by no means new and ground-breaking and I’ve seen this particular technique implemented many, many times, but it’s also something I get asked about fairly frequently and I can’t find any other blog posts detailing it so I thought I’d write it up.

To show how this works I’ve built a sample PowerPivot model in Excel. An important part of this sample model is a proper Date dimension table of course, and if you don’t have one in your data source there are plenty of ways of generating one automatically (Boyan Penev’s DateStream dataset in the Azure Marketplace, for instance, or this cool new Excel 2013 app I found today in the Office Store). Here’s the example I’ll be working with which has a Date dimension table and a Sales fact table with some values in it:

image 

On the Date dimension table I’ve added four new columns, two to handle relative dates and two to handle relative months:

Relative Date Offset
=INT([Date] – TODAY())

Relative Month Offset
=((12 * YEAR([Date])) +  MONTH([Date])) – ((12 * YEAR(TODAY())) +  MONTH(TODAY()))

Relative Date
=IF([Relative Date Offset]=0
, "Today"
, "Today " & IF([Relative Date Offset]>0, "+", "") & [Relative Date Offset])

Relative Month
=IF([Relative Month Offset]=0
, "Current Month"
, "Current Month " & IF([Relative Month Offset]>0, "+", "") & [Relative Month Offset])

The first two of these columns contain integer values that are the number of days and months between today’s date and the date in the [Date] column on the dimension table. I’ve hidden these from client tools, and then then used them in the expressions for (and as the Sort Columns for) the next two columns which show the same values in a more human-readable form. Here’s what the results look like:

image

These new columns can be used in a variety of ways. For instance, I can now put my Sales measure in a PivotTable, put Relative Date in the Filter and select the ‘Today’ value, and then put Date on columns in the PivotTable and I’ll only see today’s date:

image

image

This is because, of course, selecting ‘Today’ on Relative Date automatically filters the [Date] column down to one value – today’s date (ie January 24 2013).

I can now also build reports that show data for the current month and previous month, without showing any dates at all:

image

image

There’s one final problem that needs to be solved though: the relative dates are calculated when the Date dimension is loaded and the calculated columns evaluated, but what happens tomorrow when the relative dates need recalculating? If I was building this solution in SSAS Tabular and reprocessing your model every night automatically then I wouldn’t have this issue; in PowerPivot I need to make sure I handle this. In Excel 2010 there’s no way to automate loading data into a table, alas, so the user would have to do the refresh manually alas. In Excel 2013 I can do this using VBA very easily, by putting the following code in the WorkBook_Open() event:

ActiveWorkbook.Model.ModelTables("Date").Refresh

Refreshing the Date table also automatically refreshes your PivotTables too, which is handy. This means that when I open the workbook tomorrow (ie January 25 2013), the relative dates will have shifted accordingly and my report will show data as of January 25 2013 and not January 24 2013.

You can download my Excel 2013 sample workbook here.

18 thoughts on “Building Relative Date Reports in PowerPivot

  1. Just a thought from my side. Would it not be easier to make sure that your date dimension was marked as a date table in PowerPivot. Then when you add your date column into the rows or columns in your Pivot table you could then use the built in date filters with Excel? Which would then keep the current date or month or year. I use this often because it also teaches and allows the end user to modify their date requirements as needed. I would say that it would only not work if you were wanting to keep the current date in a filter. Then the best option would be what you blogged about above.

    Gilbert

    • Hi Gilbert,

      Yes, the date filters are another way of doing something similar, but the selections are somewhat limited compared to this approach, and sometimes users want to see headings like ‘Today’ and ‘Current Month’ in the report itself. And as you say, the Date filters don’t work with Filters or Slicers, although they are easier to set up and use.

      Chris

  2. Great tip! But I’m curious – why bury this in PowerPivot or talk about storing any derived value in a table? Why not put in a view instead…for example, here’s the SQL version I just coded into a view…
    SELECT DATE_DIM_ID AS Date_Dim_Id,
    DAY_DT AS Day_Dt,
    DATEDIFF(DD,DAY_DT,GETDATE()) as Days_Since_Today_Nbr,
    DATEDIFF(MM,DAY_DT,GETDATE()) as Months_Since_Today_Nbr,
    (CASE WHEN DATEDIFF(DD,DAY_DT,GETDATE()) = 0 THEN ‘Today’ ELSE ‘Today – ‘ + CAST(DATEDIFF(DD,DAY_DT,GETDATE()) AS VARCHAR) + ‘ Days’ END),
    (CASE WHEN DATEDIFF(DD,DAY_DT,GETDATE()) = 0 THEN ‘Today’ ELSE ‘Today – ‘ + CAST(DATEDIFF(MM,DAY_DT,GETDATE()) AS VARCHAR) + ‘ Months’ END)
    FROM EDW.CORE.DATE_DIM

    • There’s no reason you shouldn’t put this in a view. It’s just that I had to describe this technique using a technology and PowerPivot was what I was thinking about at the time.

  3. Thank’s Chris for this 100% PowerPivot solution.
    I would just add the relative week as is :
    [Relative Week Offset] = INT(([Date] – (TODAY() – WEEKDAY(TODAY();2)+1))/7)
    [Relative Week] = IF([Relative Week Offset]=0; “Current Week”; “Current Week ” & IF([Relative Week Offset]>0; “+”; “”) & [Relative Week Offset])

    • Thanks for this. Just needed to change this:
      [Relative Week Offset] = INT(([Date] – (TODAY() – WEEKDAY(TODAY();2)+1))/7)
      to this:
      [Relative Week Offset] = INT(([Date] – (TODAY() – WEEKDAY(TODAY(),2)+1))/7)

    • Yes, but only a Process Update, not a Process Full, so you wouldn’t need to reprocess any measure groups (though you would need to do a Process Default on the cube in case any aggregations had been dropped).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s