CODEWITHSUNDEEP
powerbi
zurebe-pieter
zurebe-pieter

Reputation: 3266

Convert UTC datetime to local datetime in PowerBI

In my PowerBI data source (table storage), I have a DateTime column which contains a UTC DateTime. I'm looking to convert this to the local datetime. Specifically, I also want to take into account Daylight Savings Time as well.

I have found several solutions online, but none of them takes into account DST. Anyone able to give me some pointers?

The timezone I want the user to see is UTC+2 (Belgium/Brussels).

Preferably to see in PowerBI desktop, mobile & on the website

Upvotes: 0

Views: 1892

Answers (1)

Peter Branforn
Peter Branforn

Reputation: 1687

I am answering this old question, since I found this question as a first hit in a search and it is very common and often asked question. Note that this applies mostly to data sources that are "implicitly" assuming UTC but not storing this information with the row/field values, such as a SQL-Server source with "DateTime" columns.

For querying data, thus reading from a table, you can do this:

This approach is dynamically considering the timezone by client (user local timezone) and thus also automatically taking DST into account is as follows:

You need to create a custom column in the "Query Editor" and need to add two more custom columns for each original column (click "Add custom column"):

  • One that takes timezone "UTC" as baseline and stores it for each entry. This is only necessary if your column does not carry it already. The formula is: "DateTime.AddZone([Time],0)"
  • One that then stores the converted local-timezone value (from the client at query/import time (!)). The formula to use is: "DateTimeZone.SwitchZone([ZoneTime],[UTC])"

This is not a beautiful way of doing this for large and complex data structures (with many tables). Thus an alternative solution is using "Dataflow" utilities from the PowerBI ecosystem to convert data in a stream like manner before using them in reports. In such a flow you add the timezone "UTC" column (step 1) before digesting it in Power BI.

Upvotes: 1

Related Questions