Time Zones

[PeterGarant]

Hi all

I have a problem with timezones. Our SQL server logs time in UTC, and our local machines are in UTC +1 (or +2 depending on DST).

So right now when I get data with time in it I know that the time is UTC, but Power BI sees it as local time when I import it.

Is there a way for me to tell Power BI that the time in the column is UTC so that I can convert it to local?

Example of output I have:

19-01-2017 13:11:20 +01:00

What I would like to see when converting to local time:

19-01-2017 14:11:20

When I convert it to local time I get the same value as the example. But if i convert it to UTC, then i get 12:11:20 (So the wrong way around).

I hope I have made it somewhat clear as to what my problem is, otherwise don't hessitate to ask.

Kind regards

Peter

[Greg_Deckler]

You could split your UTC time field so that you get the offset and then create a column that adds or subtracts that time as appropriate.

[PeterGarant]

That could be a way of doing it yeah, thanks for the idea. But I'm still wondering why there isn't a function to tell PBI what the time in a column is.

But guess that could come in a future release.

[v-ljerr-msft · Accepted solution]

Hi @PeterGarant,

Based on my test, you can just use the formula below to add a custom column to get the expected Local time. 

= DateTimeZone.RemoveZone([CreatedUTC]) + #duration(0, DateTimeZone.ZoneHours([CreatedUTC]), 0, 0)

Regards

[PeterGarant]

That works like a charm, thank you very much.