![outlook time zone issues outlook time zone issues](https://static.slipstick.com/images/comment_207722_attachment_images_1-200x200.png)
OUTLOOK TIME ZONE ISSUES CODE
If you want to understand how code below works read the post here. 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. I won’t be explaining details of using URL to fetch the local city here because it would make this post very long. 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. As you see in screenshot below this website gives me the current date and time for most of cities around the world And those websites usually consider DST correctly. And there are some websites that give you the current date and time for your specific city, country, or time zone. Fortunately Power Query can query web URL. 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. 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ĭay Light Saving is a big challenge, because each time zone, city, or country might have different day light saving time. Well above solution works like DAX method, but both suffer from similar issue Day Light Saving Time. You can also use other functions such as DateTime.AddZone in Power Query to turn the local date time to specific time zone. for turning that into Seattle time I have to set parameters to -7, and 0.
![outlook time zone issues outlook time zone issues](https://thedroidguy.com/wp-content/uploads/2016/02/S6-Calendar.jpg)
script above will turn the local time zone to NZ time. = DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),12,0)ġ2 is hours, and 0 is minutes for the new time zone. For this purpose I can use DateTimeZone.SwitchZone function to switch server’s local time to my time zone’s date and time. DateTimeZone functions has options such as fetching local time or switching time zones. Ken already has a blog post about time zones with Power Query which is a good read and recommended. Fortunately in Power Query there is set of functions for DateTimeZone. Thanks to my friend Ken Puls who mentioned this method to me in PASS BA conference, I come with this second option. Method 2 – Power Query DateTimeZone Functions This method works but has an issue which I deal with it later on. So I create a new calculation as DAX NZ TIME with this code: DAX NZ TIME = NOW() +(12/24)Īnd another for DAX Seattle Time with this code: DAX Seattle TIME = NOW() -(7/24) Or for Seattle I have to reduce 7 hours from it. So if I want to show my local time in Auckland, I have to add 12 hours to it. One easy way of solving this is to add time offset to the date/time function in DAX. 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. 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.
![outlook time zone issues outlook time zone issues](https://mailsmartly.com/wp-content/uploads/2020/07/Change-the-Time-Zone-in-Outlook-Web-App.png)
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. 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. 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. The reason is that DAX works with the date and time of the system that hosted the Power BI file. 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. If you want to learn more about Power BI read Power BI online book Power BI from Rookie to Rock Star.
![outlook time zone issues outlook time zone issues](https://www.lifewire.com/thmb/9KK-Bdz374cEU7BpSapDO2teUqE=/1920x1440/filters:fill(auto,1)/clock-2122431_1920-8882df7c42114c38a3e6e0ea629f2765.jpg)
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. 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. Some DAX functions such as Date/Time functions work on system date/time on the server their file is hosted on. Power BI is a cloud service, and that means Power BI files are hosted somewhere.