Timezone conversion: handling daylightsavings time dynamically

[hxkresl]

The following Radacad article by Reza Rad offers three different methods for handling TimeZone conversions.  Method 3 is a dynamic approach, but short on details.

http://radacad.com/solving-dax-time-zone-issue-in-power-bi

My current approach is a measure that considers the year and returns the time based on past and projected daylight savings time dates. But, I would like to try the dynamic way.  But, when I import the web file for my local time zone http://localtimes.info/North_America/United_States/Washington/Seattle/  it is in html format, the time and date are seperated, and neither allow themselves to be converted to datetime datatypes.  

Anyone implemented a dynamic solution for daylight savings time?

[v-jiascu-msft]

Hi @hxkresl,

We can merge two rows together. We will talk it later. First, if we connect to a web resource, we have to refresh to get the latest time. It seems this isn't dynamic. I think we need an time API and a time zone table here. We can use API to get the time of (UTC+0) and get time zone from time zone table. Finally we get the time for a special time zone. I didn't find a good API. So I won't post anything about it.

Best Regards!

Dale

[hxkresl]

i had the following problems with above approach.

1. When i refresh the report,  the transpose in Table 1 is undone.

2. unable to change datatype to date time. I did not merge Time and Date, because neither allowed datatype conversion from text to datetime.  

If above weren't issues my plan was to join Datetime column to any table where I need to make time conversion. 

[yingweiy]

Web.Page is risky and PowerBI will enforce it to refresh through a gateway even when the data source it accesses is cloud based.

An alternative way to get local time dynamically is by calling an API and use Xml.Document to parse the response. This will avoid to use a gateway to refresh.

Here are the steps I figured out:

1. Open your PBIX file and click "Edit Queries"
2. In the query editor, choose the query used to get the local time. under "Home", click "Advanced Editor". In the editor window, replace your query with this one:

let Source = Xml.Document(Web.Contents("http://api.timezonedb.com/v2/get-time-zone?key=LGN45QRJHMMD&format=xml&by=zone&zone=PDT")),

    Value = Source{0}[Value],

    Value1 = Value{12}[Value]

in

Value

    3.  You will need to replace the highlighted key with your own key (to get your own key, you can register a free account from timezonedb.com, and also replace the zone with your time zone, e.g. for Texas/Dallas, you can use CDT for central daylight saving time).

    4. Then update your report to use the result from this query as local time.

Hope this is helpful.

Yingwei