PowerBi Service Changes Local Time to UTC

[myou]

This is really frustrating

I have tried everything

In Power Bi Desktop all dates are fine

Then suddenly in power bi service the date changes 1 day back

The steps are like below

1. I have Leave STart date in UTC time

2. Then I convert it to Datetimezone

= Table.TransformColumnTypes(#"Removed Other Columns2",{{"Leave Start Date", type datetimezone}})

3.  Then I convert it to local time

= Table.AddColumn(#"Changed Type1", "LeaveStartDate", each DateTimeZone.SwitchZone([Leave Start Date],4,0))

 4. Then I tranform it to date

In PBI Desktop its fine, when I publish it suddenly the date changes to 28 Dec for example id =83507

[Adamtall]

Hi,

There is currently no way to change time zone in Service. 

But 🙂

I had the same problem couple of month ago.

Powerquery custom column. (maybe not the right +2 for you, but for me it was.)

DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),+2)

Hope it helps! 

/Adam

[v-lid-msft]

Hi @myou ,

Sorry for that, but we cannot reproduce this issue on my side, we tested with the "LeaveStartDate" as datetimezone type and date time type, does other date have this issue on your report?

All the queries we used are following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjA2NTBX0lEyNNI3stA3MjC0VDA0sDIAIYUAXwVtMEspNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FilledFormId = _t, #"Leave Start Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FilledFormId", Int64.Type}, {"Leave Start Date", type datetimezone}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "LeaveStartDate", each DateTimeZone.SwitchZone([Leave Start Date],4,0)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LeaveStartDate", type datetimezone}})
in
    #"Changed Type1"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjA2NTBX0lEyNNI3stA3MjC0VDA0sDIAIYUAXwVtMEspNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FilledFormId = _t, #"Leave Start Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FilledFormId", Int64.Type}, {"Leave Start Date", type datetimezone}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "LeaveStartDate", each DateTimeZone.SwitchZone([Leave Start Date],4,0)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LeaveStartDate", type date}})
in
    #"Changed Type1"

Please also refer to this post about this scenario: https://community.powerbi.com/t5/Community-Knowledge-Base/Time-in-PBI-Service-is-inconsistent-with-t...

Best regards,

[myou]

@v-lid-msft

In power bi desktop I dont face this issue, its when I publish to power bi Service

Its fine for 1 min then after 10 mins when I refresh, the time is changing 

[v-lid-msft]

Hi @myou ,

We can try to set the column as date/time/zone in Power Query Editor and format it as date type in data model to solve it.

We have test refreh it several times on Power BI Service.

Best regards,

[myou]

@v-lid-msft  Unfortunelety still facing this issue

Only way to fix it is to do conversion from sqlserver

I have to ask, is it related any sort of way to the database?

Am reading from Azure Database stored in cloud, and its stored in this way 2018-07-31T20:00:00.000Z