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:

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.