cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Maintain Daylight Savings Table

Hi,

 

It looks like the only way to achieve proper date display in AEST (Australian Eastern Standard Time) which takes into account daylight saving is to query a table that stores the hours offset by year.

This is based on the blog post:

https://intellitect.com/convert-utc-local-time-daylight-savings-support-power-bi/

 

Is there a way to dynamically load this data as a Power Query based on the current year?

 

I do not know of a website that can provide this data in a form that is querable in Power Bi.

3 REPLIES 3
nickdewitt
Frequent Visitor

One way I managed to get this working Dynamically, was by setting up a flow HTTP endpoint that took a date a UTC input and returned a date in my timezone, using the convertFromUtc function to convert to a specified time zone (GMT in my case). The only problem here is, it's a premium connector and it runs once for every datetime you supply. Even for a small dataset, I was running the flow like 800 times per refresh. That seemed way too much, especially given the recent licensing changes.

 

You could quite easily replicate that with a web server, running any code that has a library for changing between timezones (e.g. .NET)

 

One one of the blogs linked, the following statement was made:

Update:  The Power BI service was recently updated such that DateTimeZone.ToLocal() now uses the client time zone which means the solution below is no longer needed to resolve the problem discussed.

 

After wasting almost 2 days, I found this not to be true. Not unless it's converting to the same time zone but ignoring daylight savings time as it consistently changed back to UTC  (Which is the same as GMT out of DST). I need my times to be right during BST.

 

There are also some paid API's that you could use (e.g. https://www.timeanddate.com/) but the ones I found are either not free, or not suitable for the task.

 

I ended up going down the table route as it was the only reasonable avenue left to go down. I have stored a table like the following on an SQL server for re-use, but you could also just enter the data into Power BI Desktop directly:

 

YearUTC DST StartUTC DST End
201726/03/2017 01:0029/10/2017 01:00
201825/03/2018 01:0028/10/2018 01:00
201931/03/2019 01:0027/10/2019 01:00

 

I have then written a couple of functions in power bi in order to make the conversion a little easier, and any future rework easier for myself:

 

IsDaylightSavingsTime function:

 

(dateTimeValue as datetime) as logical =>
let
    sourceYear = Date.Year(dateTimeValue),
    rowValue = Table.First(Table.SelectRows(DaylightSavingsTime, each [Year] = sourceYear)),
    isDst = rowValue[Start] < dateTimeValue and dateTimeValue < rowValue[End]
in
   isDst

 

ConvertUTCtoGMT function:

 

(utcDateTime as datetime) as datetime =>
let
    Source = ""
in
    if IsDaylightSavings(utcDateTime) then (utcDateTime + #duration(0,1,0,0)) else utcDateTime

And then wherever I want to convert a UTC date to GMT, I just use the following:

 

= Table.AddColumn(#"Filtered Rows1", "new_taskstart_gmt", each ConvertUTCtoGMT(DateTimeZone.RemoveZone([new_taskstart])), DateTime.Type)

 

The nice thing about this, is that if/when Power BI get round to implementing a datetime.toLocale function, I just change my ConvertUTCtoGMT function to the following, and i'm done, I can throw the table away:

 

(utcDateTime as datetime) as datetime =>
let
    Source = ""
in
    datetime.toLocale(utcDateTime,'GMT')

 

It's really worth noting that when you first publish a report from Power BI desktop, the data you pushed up has been calculated on your local machine, with your locale. What you see just after you publish is the same as what you will have seen in Power BI Desktop, but the next time the Power BI Service refreshes your data, it will calculate in the context of it's locale, UTC, and everything will be out of sync again.

v-chuncz-msft
Community Support
Community Support

@Anonymous ,

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/UTC-to-AEST/m-p/187319#M82393

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi,

I have read that link and it looks like you still need to add values manually to the table. I'm looking for a solution that can do this automatically.

 

Thanks

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!