cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

Re: Maintain Daylight Savings Table

@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.
Highlighted
Anonymous
Not applicable

Re: Maintain Daylight Savings Table

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

Highlighted
Frequent Visitor

Re: Maintain Daylight Savings Table

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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors