Forum Discussion

myou's avatar
myou
Helper II
6 years ago

PowerBi Service Changes Local Time to UTC

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

Need Help
Show and Tell
Tips and Tricks

5 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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-the-local-time-non-UTC/ta-p/162367


    Best regards,

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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