# 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:

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:

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:

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:

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.

## 22 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. Gary Melhaff says:

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

• praveen says:

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

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

• Darren says:

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)

• Hi Darren,
It’s just because my regional settings are in french and the delimiter for dax formula is ‘;’ instead of the ‘,’.

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!

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

• Kenneth Clebak says:

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.

6. Rakesh says:

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

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

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

• powerpivotisfun says:

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

7. […] 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. […]

8. […] I created a relative date column using this code from Chris Webb http://blog.crossjoin.co.uk/2013/01/24/building-relative-date-reports-in-powerpivot/ […]

9. Marius says:

It is done very nice and clear.
In my case though, I have a moving Relative Period that I have difficulties to implement.
In my organisation we run Events. Each event has a start date. The Relative Period I would like to build would take the event start date as a calculation threshold (instead of TODAY() like you used in all your calculations)
The problem is that if I put =INT([Date] – Event[Event Start Date]) it will obviously error out due to the fact Event table has many rows and only one row can be passed for calculation.
Is there any way I can narrow down the context so the function will work?