Offset Columns for the Date Table: Flexibility in Relative Date Filtering for Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

I have written previously provided an all-in-one script for the date dimension, and also wrote an article about the relative date slicer. However, sometimes you cannot use the relative date slicer (timezone issue can be one example), adding offset columns help to overcome that challenge, and still be able to do flexible relative filtering in Power BI by the date field. In this article, I’ll share the script and explain how this works.

Updated version of Date Dimension with Offset Columns

I have just updated the date dimension script here to have the offset columns, the new table layout has four more columns in it for offsets.

Custom generated offset columns

Script

The script to generate these columns as part of the main date dimension and you can get it from the below article with all the details of how to build this table in Power BI;

What are Offset Columns?

Offset columns are representing the difference of that date (in days, months, quarters, and years) to today’s (day, month, quarter, and year). For example, if today is the 8th of May 2020. the Day offset for today is 0, for 7th of May 2020 is -1, for 6th of May -2, etc.

There are four offset columns in the date dimension above, Day offset, Month Offset, Quarter Offset, and Year Offset. Apart from the day offset (which works fine just as is), the other three are calendar month, quarter, and year. Meaning that if today is 8th of May 2020, then the Month offset for the 1st of May 2020 is also 0, but the month offset for the 30th of April 2020 is -1. Same for the year and quarter offset columns.

here are some values in these columns:

Offset columns

How you can use Offset Columns?

You can use the offset columns in multiple places, here are a few of those:

Custom Relative Slicer: Offset Slicer

You can set up your own custom relative slicer as a set of offset slicers. this especially helps if, for some reason, you cannot use the normal relative date slicer. The timezone issue is one of the examples of such limitation, which can be resolved with this approach.

In the below screenshot I have used the four offset columns in four slicers, and you can see how they can be used.

Using Offset Date Slicers in Power BI

The negative values in the slicer means going back to past periods. Positive values will be future periods, and the current period will be zero. Based on that if I want to create a report to be for the past calendar month, I can simply use a filter like below;

Filtering the report for the last calendar month

Using in DAX Expressions

This might not be the most popular usage of the offset columns, because DAX is very powerful with the time intelligence functions, and myself never use offset columns for those calculation, I just use the time intelligence functions combination to achieve the result. However, if you even need an alternative approach, these offset columns can be an aid in your date-based DAX expressions.

Timezone Challenge

If you take a closer look at the script for the date dimension, you’ll notice that there is a variable defined as Today. that variable uses the system’s date, which if you publish your Power BI report to the service, will not be your local timezone’s date.

If you want to overcome this challenge, use the approaches I mentioned in this article, and then use the result of that as the input of Today’s variable. Then you will have a proper relative date slicer working for your local timezone. I’ll write about this more in detail in another article.

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply