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

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


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.


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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog:

Leave a Reply