Relative Date Slicer for Your Local Time Zone in Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

If you have used the relative date slicer and you are not living at a timezone close to UTC, then you have seen that the Power BI Date slicer is not much of a use, because you have to still adjust it with your local time zone. I have previously written a couple of articles related to this issue and ways to solve them. Now in this article, I’ll explain these fully in one solution that you can easily use and get it working without any problem.

Time Zone for Power BI

As I mentioned in detail in this article when you publish a report to the Power BI service, the Power BI server’s date and time is based on UTC, and it might be different from your local timezone;

The Power BI service shows the UTC time zone

If you haven’t spotted the difference in the above screenshot, look closer at the AM/PM difference! I am living in New Zealand, so the difference is 12 hours right now!

Relative Date Slicer

Relative Date slicer is a great way to filter data by the date relative to today’s date. Here are some relative period options you can choose from:

Relative Date Slicer

The Problem

The problem when your local time zone is so apart from the UTC timezone is that, often the today’s date in the website is not really today, it might be yesterday or tomorrow depends on where you are living.

So then if you have a filter saying filter the data for yesterday, it might then be for two days ago, or today! the whole concept of relative date slicer would not work in that circumstance.

Solution Part 1: Build Your Local’s Today

There are multiple ways in which you can create a local today’s time and date, and explained them in detail in this article. Methods are in high-level as below;

  1. Use DAX expressions to add or deduct some hours from NOW()
  2. Use Power Query functions to get the local time zone
  3. Use a web query to get the local timezone from a Web API.

Among those methods, my preferred method is the 3rd method, because it would work just fine locally, and in the Power BI service. find that method in detail explained here:

Below is a screenshot of the correct local date and time calculation through a Web API call (Method three in the above article);

Correct Local date and time

Solution Part 2: Offset Date Slicer

Getting the correct local date and time is part of the solution, because still if I use the relative date slicer in the above scenario, it won’t work. The second part of the solution is to create a relative date slicing and dicing experience using an Offset Date Slicer method I explained in this article.

Offset Date Slicers

An offset date slicer is a set of slicers that I can use with relation to some offset columns for date, month, quarter, and year in my calendar or date table. This means that for this method, you need to have a custom Date table. and here in this article, I explained in detail how you can create a custom Power BI date table with the full script provided.

The trick is that the date table should be created considering Today to come from the query that is generated through WebAPI:

As you see the first few lines of the above code generates a local date and time. and that then will be used to generate the offset date columns.

Set this table to be Marked As Date table, and then you are good to go. you can create offset date slicer columns and use Power BI report in the Power BI service to work with your local date and time.

Let me know your thoughts and any questions down below at the comments.

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.

2 thoughts on “Relative Date Slicer for Your Local Time Zone in Power BI

  • Hi Rezza,
    Will you be posting a sample .pbix file which demonstrates your preferred method (ie the 3rd method),

    • Hi Steve, the code is all available in this post, you just need to copy and paste it into a blank query, and change it for your time zone
      Cheers
      Reza

Leave a Reply

%d bloggers like this: