PowerBI Service shows date time in UTC instead in local after Refresh

[mmustapic]

PowerBI source is SharePoint list.

Modified and Created are acquired in UTC and PBI is aware of that after Changing Type to DateTimeZone.

After applying change type once more but this time with Locale it converts the zone also according to set value and it shows correctly in PBI Desktop. After uploading a file to PBI Service it is also shown correctly.

The problem is after refreshing the report in PBI Service, the time is then shown in UTC again and it makes a difference of a day when looking at date.

#"Changed Type" = Table.TransformColumnTypes(#"Data source",{{"Modified", type datetimezone}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Modified", type datetime}}, "hr-HR")

The same happens with all other workarounds of DateTime zones conversion which I have used.. PBI Service doesn't refresh it to correct zone even if it's working fine in PBI Desktop.

[orlandopowerbi]

Hello, did you manage to solve the issue? The exact thing happens to my report. Showing correctly datetimes on desktop but shifted back to UTC on power bi online. Any update would be appreciated 🙂

[v-lid-msft]

Hi @mmustapic ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @mmustapic ,

The culture is just change the format of value. We can use the DateTimeZone.SwitchZone Function to change the Time Zone manually.

Create a custom use this function.

DateTimeZone.SwitchZone([Date],10,0)

Replace the origin Date Column

Here is All the M Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ31DdUMDS2MjAAIqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetimezone}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTimeZone.SwitchZone([Date],10,0)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}})
in
    #"Renamed Columns"

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mmustapic]

Hi,

@v-lid-msft thanks for the answer but unfortunately switchzone does not solve the problem 100%. As we are using DST, the switch zone function values should be updated every now and then. And to do that on every report is time consuming and unacceptable.

Also making some functions which would calculate this is also out of acceptable solutions. It's basically hardcoding the logics which may change based on the laws.

ToLocal would work nicely if there is a setting to define it and if the setting is respected in PowerBI Service when the refresh is happening. (ToLocal in PBI Service currently means utc time but same works in PBI desktop..)
Also, what would be helpful is if in SwitchZone we could define the time zone in abbreviations: CET, PDT, PT... (CET is sometimes +1 and sometimes +2, depending on DST)
https://www.timeanddate.com/time/zones/

So, to shorten this, what would suit is the option to define the display timezone with compliance to DST and without hardcoding the number or logics. SwitchZone function misses some key things to achieve this.. And ToLocal doesn't work in PBI Service.