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.
You can download my Excel 2013 sample workbook here.
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
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.
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])
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.
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 ‘,’.
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?
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.
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).
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
Hi Miguel, nice to meet you! Good post – yes, it is pretty much the same approach we’re describing.
Subscribing to your blog now so I can keep up to your posts – Awesome blog!
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?
Have anybody made it for WEEK OFFSET? I’m thinking about WEEKNUM and not WEEKDAY as suggested in previously comments. The problem here, is that it’s not a common max value for weeknum since it could be 52 or 53. I also want this for ISO-weeks, which means week 52 and 53 could be in the start of the year.
This is all achievable via a rich date dimension encapsulated with a view with built in offsets based on current date so what you want to filter on or range search are simple attribute offsets.
Didn’t quite understand you there Gary, but after a bit more thinking I figured out how to do it. So here’s for the rest of you wondering the same: RELATIVE WEEK OFFSET = INT((DATEVALUE([DATE])+((WEEKDAY([DATE];2)-1)*(-1)))-(DATEVALUE(TODAY())+((WEEKDAY(TODAY();2)-1)*(-1))))/7
This checks the days between the start of the week (monday) for today’s week with the start of the week for my DATE-column, and divides by 7.
Hey Chris- came across this blog when trying to achieve a “Relative BUSINESS Date Offset”. e.g. Saturday & Sunday are not business days in my company. So if it’s Monday and I filter to -2, it shows me Thursday & Friday of the previous week, not Saturday & Sunday. Any ideas ? Great blog, thanks !
It will all depend on how you populate that relative date column. You would need to put some other value than -2 in the rows for Saturday and Sunday, making the code rather more complex. In fact I’m not sure I would want to do it in DAX at all, it’s probably much easier in Power Query. Hmm, a good subject for a future blog post.
Hi Chris
I want the DAX for calculating the previous month sales and my sales fact table is having only month and year column. There is no date column
Also the previous month should be 12 instead of 0 when the current month is 1
Regards
Arun