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.
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:
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.
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;
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
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: https://radacad.com/blog.