Solving DAX Time Zone Issue in Power BI

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:

DAX NZ TIME = NOW()+(12/24)

and another for DAX Seattle Time with this code:

DAX Seattle TIME = NOW()-(7/24)

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.

= DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),12,0)

 

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:

let
    Source = Web.Page(Web.Contents("http://localtimes.info/Oceania/New_Zealand/Auckland/")),
    Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),
    date = #"Changed Type"{1}[Column2],
    time=#"Changed Type"{0}[Column2],
    datetime=DateTime.FromText(date&" "&time)
in
    datetime

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

2016-05-16_23h08_59

 

Method 3 Revisited – with Xml.Document

the method mentioned with web query uses Web.Page Power Query function and hence it requires gateway setup to work. Thanks to Yingwei Yang from Microsoft team who suggested this approach; there is another way which doesn’t require gateway setup: using Xml.Document function. Let’s go through that solution.

Timezonedb.com is the website that has an api to return timezone information, fortunately api is free to use. you need to register for the api;

2017-08-25_04h43_22

after registering you will receive an API Key which you can use in a api url as below:

2017-08-25_04h47_49

To learn more about API read this link.

Now that we have an api to work with, we can use Xml.Document function to read data from it. here is how to do it. start with a Blank Query in Power Query, then go to View -> Advanced Editor and replace the whole query with below script:

let 
    Source = Xml.Document(Web.Contents("http://api.timezonedb.com/v2/get-time-zone?key=XYZ&format=xml&by=zone&zone=PDT")),
    Value = Source{0}[Value],
    Value1 = Value{12}[Value]
in
    Value1

This method is the recommended method from all above options.

2017-08-25_05h01_00

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.

 

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

28 thoughts on “Solving DAX Time Zone Issue in Power BI

  • Hi Reza, thanks for sharing this – it’s very helpful and clearly explained.

    For one client I’m running PBI Desktop from a generic Azure VM, it couldn’t access localtimes.info due to javascript requirements. While surfing timeanddate.com from that server (using IE), I stumbled across the “Full Screen” page which has really simple content – quite easy to shred in PQ:

    http://www.timeanddate.com/worldclock/fullscreen.html?n=22

  • 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

  • Hi Reza,

    Would there be a way to let ‘Method 3: Revisited’ automate in the Power Query script when it should place (in my case) CEST (summer time) in the API url and when to place CET (winter time) in the API url?

    So taking Amsterdam time as an example: after Sunday 27 October 2019 03:00 CEST the script result should switch from CEST to CET (in the API url), and then on Sunday 29 March 2020 02:00 it should switch from CET to CEST again.

    Thanks for your reply!

      • Actually Robin and Reza the solution is much simpler than finding a different service (if I’m understanding the problem correctly). You can still use the service in the method 3 above but instead of using the timezone Abbreviation use the timezone Name. I.e. in my case instead of using “…zone&zone=AEDT” I am using “…zone&zone=Australia/Hobart”. I have only registered a free account with timezonedb.com and this query is working perfectly for me.

        • Hi Karl,
          Yes, great. that is what I suggest to, get the information based on the location, not timezone.
          Thanks for sharing your experience

          Cheers
          Reza

  • Trying to use the standard relative date filter and filter for the last one day eg yesterday. The dates are straight dates with no times.

    Works fine on the desktop, when published the Slicer works differently in the morning and afternoon as UTC changes date at midday/13:00 depending on Day light saving.

    Then you get a report with data for yesterday in the afternoon and the day before yesterday from midnight to midday/yesterday.

    Have tried adding offsets to the dates, using a web site date as above, looks for different filters, Googled asked Microsoft, etc

    Any suggestions for a solution please?

    • That is a good topic for a blog post 😉
      I never had that requirement from my clients. but thinking of that, If I want to implement such a thing, this might be how I would do it.
      In Power Query, I’d use the method #3 mentioned in this post to fetch the local time (that works even on the service, and it brings both date and time considering DST and everything), then I’d put that with an offset in my date table. as a column saying 0 means the current date, -1 means yesterday…
      Then in the report, I’d use a slicer from this offset column (not a relative date slicer), just a normal slicer would work.
      This is only one of the ways that you can do it out of many.
      Cheers
      Reza

      • Hi,

        We are also looking for a similar solution on our side as well with relative date slicer. We are using SSAS tabular model (direct query) as our data source and the date dimension table has the date column only (no time information) which, is causing most of our reports with the relative date slicers go blank after 4/5 PM our time (due to date change in UTC). Tried all the available methods using power query/dax but none of them seem to work. This would have been much easier to set a locale on the power bi service and get it done with. 🙂

        • Hi.
          With SSAS Live Connection, you cannot use Power Query methods mentioned here.
          Do you have users connecting to the Power BI reports in the service in one timezone? or multiple timezones?
          Cheers
          Reza

  • I’ve tried method 3 which works great on PowerBI desktop, however, when publishing to PowerBI online the date/time tile does not refresh on the report. Is there a reason for this?

    • After publishing it to the service, you have to go to the database settings and edit credentials for it. Have you done that?
      Cheers
      Reza

  • Hello,

    I have two questions as I am trying to get my report to run in EASTERN time with relative date filtering:
    1. I tried the updated solution #3 but I need to know how to add the blank query to my current query report.
    2. I also tried creating a calculated column and added a DAX formula (LocalDate = IF(DATEDIFF(NOW(), UTCNOW(), DAY) = -4, Sales[Date] – 1, Sales[Date])) but it is just showing a static date/time. Is there a way to call the API that you provided in a calculated column?

    Please note I used this DAX formula to create the blank query which gave me the time correctly from the updated solution #3: let Source = Xml.Document(Web.Contents(“http://api.timezonedb.com/v2/get-time-zone?key=XYZ&format=xml&by=zone&zone=EDT”)), Value = Source{0}[Value], Value1 = Value{12}[Value]in Value1

    Thank you and have a great day!
    Alex

    • Hi Alex
      This is part of the overall solution.
      once you got the current date in the timezone you are looking for (which you said you achieve it with the method 3), then you need to create offset columns in your date table. this means that you would get a better result if you have a custom date table, here is a sample Power Query date table. the offset columns would have things such as Year Offset, Month Offset, Day offset etc, which then you can use those in a slicer. it won’t be using the relative date slicer, but it can work similarly.

      Cheers
      Reza

  • Little late to the party here but a method I employ after 3(!) API services flaked out on me.

    A query to SQL Server (>=2016 or Azure)
    SELECT GETDATE() AT TIME ZONE ‘Central European Standard Time’ as CEST

    Works local and in the service, and is reliable. The only requirement is access to a SQL server to make the query to.

    • Hi Benjamin
      That is indeed a very good method if you have SQL Server as a data source. However, that is not always the case
      Cheers
      Reza

    • Hi
      It is possible, but it is not recommended
      because Day Light saving dates changes every year, and there is no way in DAX that you can know what is the day light saving for a year that still has to come. your DAX expressions will be static then. Unless you populate a table of each year and the day light saving days of those years and then use DAX to lookup into that table.
      Cheers
      Reza

  • Thank you SOOOOOOOOO much for providing all the help you do. Somethings the solutions are simple and straightforward but impossible to figure out without your tips. Much appreciated.

Leave a Reply