Solving DAX Time Zone Issue in Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-05-16_21h08_11

Power BI is a cloud service, and that means Power BI files are hosted somewhere. Some DAX functions such as Date/Time functions work on system date/time on the server their file is hosted on. So If you use DAX functions such as TODAY() or NOW() you will not get your local date/time, You will fetch server’s date/time. In this blog post I’ll explain methods of solving this issue, so you could use Power BI to resolve your specific time zone’s date and time. If you want to learn more about Power BI read Power BI online book; Power BI from Rookie to Rock Star.

Defining the Problem

Using DAX functions on your local Power BI file is totally different from what you will see in Power BI website especially when date and time functions has been used. The reason is that DAX works with the date and time of the system that hosted the Power BI file. Power BI is a could based service, and that means Power BI files will be hosted on a server somewhere in the world, that might not be on the same time zone as your city is. So as a result when you used functions that works with the current date and time; such as TODAY() or NOW() in DAX you will not get your local current date and time. At this stage there is time zone feature in DAX functions to help resolving this, so I suggest few options to resolve it as below.

Screenshot below shows a Power BI report published on Power BI website, and the result of DAX NOW() function there compared with the local date/time on the client system. Please note that you won’t see this anomaly in Power BI Desktop, because in that case file is running on your local system, and the result would be your local date/time, you will only face this issue when you publish solution to Power BI website.

2016-05-16_20h58_49

Method 1 – DAX Formula Manipulation

One easy way of solving this is to add time offset to the date/time function in DAX. Power BI date/time seems to be GMT. So if I want to show my local time in Auckland, I have to add 12 hours to it. Or for Seattle I have to reduce 7 hours from it.

So I create a new calculation as DAX NZ TIME with this code:

and another for DAX Seattle Time with this code:

Here is corrected result as below;

2016-05-16_21h08_11

This method works but has an issue which I deal with it later on.

Method 2 – Power Query DateTimeZone Functions

Thanks to my friend Ken Puls who mentioned this method to me in PASS BA conference, I come with this second option. Fortunately in Power Query there is set of functions for DateTimeZone. Ken already has a blog post about time zones with Power Query which is a good read and recommended. DateTimeZone functions has options such as fetching local time or switching time zones. For this purpose I can use DateTimeZone.SwitchZone function to switch server’s local time to my time zone’s date and time.

 

12 is hours, and 0 is minutes for the new time zone. script above will turn the local time zone to NZ time. for turning that into Seattle time I have to set parameters to -7, and 0.

And here is the result set:

2016-05-16_21h49_59

You can also use other functions such as DateTime.AddZone in Power Query to turn the local date time to specific time zone.

Well above solution works like DAX method, but both suffer from similar issue; Day Light Saving Time. This is the reason that if you try code above in summer or winter you might get different result!

Method 3 – Web Query with Power Query

Day Light Saving is a big challenge, because each time zone, city, or country might have different day light saving time. even same city might have different dates for DST (Daylight Saving Time) for different years! Power Query is intelligence enough to help with Time Zone issue, but doesn’t have a directory of all DST times for all time zones. Fortunately Power Query can query web URL. And there are some websites that give you the current date and time for your specific city, country, or time zone. And those websites usually consider DST correctly. One of these websites is TimeAndDate.com . As you see in screenshot below this website gives me the current date and time for most of cities around the world;

2016-05-16_23h16_50

 

In Power Query we can use functions such as Web.Page() and Web.Contents() to read tables in a web page, and then fetch part of it that we want with some other transformations. I won’t be explaining details of using timeanddate.com URL to fetch the local city here because it would make this post very long. I just refer you to my other post about reading some date/time information for different time zones which is similar to method I’ve used here. If you want to understand how code below works read the post here. For this part I will be using another website which gives me current date and time in Auckland, and here is Power Query code:

And here is the result with the correct DST and time zone;

2016-05-16_23h08_59

 

Other Methods

At the time of writing this post, I’ve only thought about these three methods. You might have an idea about another method. In that case, don’t hesitate to share it here.

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

5 Comments

  • Thanks Reza for this great post.
    I have a slightly different but related issue that I am trying to resolve. I have a set of dashboard that the managers want to see over time report and completed work orders for yesterday by default. We are in Portland, OR so on PST time zone. Everything works fine until 4:00PM when the report break. I am using the below calculation to flag Yesterday:
    Yesterday = IF(DimDate[Date] = TODAY()-1, 1, 0)

    How do I change this calculation so that it still work on the Power BI Service past 4:00PM? the Desktop version works fine.

    Thanks;
    Andrew

    • Hi Andrew,
      If your reports break at 4pm, it means your Power BI server is located somewhere with 8 hours difference of your local time. Have you checked that? if that is the case you should do a Today – 1 – 8/24
      however this method won’t consider DST. If you want DST to be covered you should do the third method with Power Query

      Cheers
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">