Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Building Relative Date Reports in PowerPivot

with 17 comments

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.

Written by Chris Webb

January 24, 2013 at 2:55 pm

Posted in DAX, PowerPivot

17 Responses

Subscribe to comments with RSS.

  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

    myfriendjoobs

    January 25, 2013 at 4:30 am

    • 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

      Chris Webb

      January 25, 2013 at 8:22 pm

  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

    Gary Melhaff

    January 26, 2013 at 11:07 pm

    • 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.

      Chris Webb

      January 26, 2013 at 11:23 pm

  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])

    fbrossard

    January 27, 2013 at 5:19 pm

    • How would this be done for year, i want to get the current year

      praveen

      July 16, 2013 at 10:04 am

      • The method would be the same – you’d just need an expression that returned whether the year was the current year or not.

        Chris Webb

        July 16, 2013 at 2:02 pm

  4. [...] Step2 « Analyses Glissantes »). Merci à Chris Webb pour son exemple d’implémentation sur les jours et mois relatifs qui je me suis empressé d’enrichir avec les semaines relatives [...]

  5. Hi Chris! This is a fantastic solution for us, however we are in need of being able to create a Relative Quarter and are struggling with what the calculation for that may be. Any help you could provide would be amazing! Thanks!

    Mandi Albano (@mandialbano)

    February 20, 2013 at 12:35 am

    • What do you have at the moment, and what does it return?

      Chris Webb

      February 20, 2013 at 8:13 pm

    • Hi Mandi. I just stumbled across this and was curious if you found a solution to Relative Quarter as I am looking for the same thing.

      Kenneth Clebak

      June 20, 2013 at 9:36 pm

  6. If this solution is applied to MOLAP Cube, date dimension would need processing every day isnt it?

    Rakesh

    March 4, 2013 at 1:08 pm

    • 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).

      Chris Webb

      March 4, 2013 at 1:11 pm

  7. Hey Chris!

    I recently did a guest post in Rob’s blog and I didn’t know that you already had one about this topic.
    Let me know what you think about it!

    http://www.powerpivotpro.com/2013/03/guest-post-always-show-yesterday-today-or-tomorrows-data

    powerpivotisfun

    March 28, 2013 at 10:04 pm

    • Hi Miguel, nice to meet you! Good post – yes, it is pretty much the same approach we’re describing.

      Chris Webb

      March 29, 2013 at 10:34 am

      • Subscribing to your blog now so I can keep up to your posts – Awesome blog!

        powerpivotisfun

        March 29, 2013 at 11:57 am

  8. […] Date.IsInCurrentDay() compares a date with the current system date and allows you to see if a date is today’s date. There are a whole bunch of other interesting functions like this such as Date.IsInPreviousWeek(), Date.IsInCurrentYear() and so on, useful for creating relative date dimension tables like the one I describe here. […]


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

Follow

Get every new post delivered to your Inbox.

Join 3,309 other followers

%d bloggers like this: