I recently had a strange issue where I was converting a BST datetime to a date using transform to date and in Desktop it looked fine, but when publsihed out to theservice the day was a day earlier for any datetime added as 1/9/21 00:00:00 BST. This was becasue this is 31/8/21 23:00:00 UTC and the service can't handle this. Even converting the datetime using "Use Locale" in Desktop didnt solve this.
I spoke at length to Power BI support and unbeleivably this is a major bug thgat the service cannot handle any time other than UTC. i am sure there are thousands of people out there iwth the same issue. Many people may not realsie the data on the service is wrong as their desktop model looks correct. I am stunned Microsoft prioritise fancy new visuals over havcing correct data!!
The solution I have been given is extremly horrible, especially if you have multiple datetime columns, but I'm postting it here for general information
1) This scenario we tried on the call and as there is currently no way to change the time zone in Service. You can try changing the time to the locale in Power BI desktop as below and give a try. (This made no differnece for me)
2) Assuming currently we have the ‘Time’ and ‘UTC’ column in a table, where [Time] stores the actual UTC time, [UTC] stores the changed hour count between the User and UTC. Based on the different Time Zone, UTC values change within -12 to 12 range.
When data loaded into Power BI, open Query Editor, under the Add column tab, click Add Custom Column, then under the formula part, copy and paste the following: DateTime.AddZone([Time],0)
Click OK. This step adds the default UTC Time Zone (0 here) under the time stored in [Time], to the newly created ‘ZoneTime’ column. After that, we need to create another column to switch the time zone to the client, also click on the Add custom column, then copy and paste the following formula: DateTimeZone.SwitchZone([ZoneTime],[UTC])
After that, we have successfully added the time Zone information into the time. Then we need format the column into ‘date/time/timezone’ format.
Select ClientTime column, navigate to Transform tab, choose data type and select Date/Time/TimeZone:
After that, click close and apply.
Till then we should have finished converting the time into Client local time.
3) This formula can also be used to change time (here we have taken Australia time) : Change time = UTCNOW() + (11/24) & "AUS"
4) let
Source = Web.Page(Web.Contents("Replace this with your local time website.")),
Data1 = Source{1}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),
date = #"Changed Type"{1}[Column2], time=#"Changed Type"{0}[Column2],
datetime=DateTime.FromText(date&" "&time)
in
datetime
5) change = DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),11,0) to = DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),Parameter,0)
The following limitations and considerations currently apply to the relative date range slicer and filter [Applicable if you are using them]:
- Data models in Power BI do not include time zone information. The models can store times, but there's no indication of the time zone they're in.
- The slicer and filter are always based on the time in UTC, so if you configure a filter in a report and send it to a colleague in a different time zone, you'll both see the same data. However, if you aren't in the UTC time zone, you might see data for a different time offset than you expect.
- Data captured in a local time zone can be converted to UTC using the Query Editor.
