Forum Discussion
Resolver IUTC, GMT Issue for Reports Built from D365
Hi All,
Apologies for getting back late to this.
I resolved the issue but my solution is just a patch, i would prefer something more substantial
so I have a ticket open with MS to discuss.
Anyway, so I ran a series of tests on one single time entry and followed it from the power query editor to Power BI desktop to Power BI Service.
First I set the data to Date\time to dsiplay the time stamp also. Then I created a new column in the Power Query editor to copy the date column (column with my time entries) and apply some timezone changes to it.
I done this for three times 0, +3, -3.
For example, DateTimeZone.SwitchZone([date], 3))
As you can see the time entry is timestamped at midnight and as you can see from the 0 row the entry actually goes back a full day when published to the service! This is why my small changes of 1 or 2 hours wasn't making any difference.
To verify my finding I just went with the original date column (took out the new query) and followed the same time entry through the desktop and service and it too appeared a day before in the service.
To get around this I used a simple DAX expression dateadd(timeentries[date],1,DAY)
Which creates a new column with one day added to the time entries. Now my desktop data is one day ahead but it's perfect in the service so the users are happy.
This is not my preferred long term solution and I'm working with MS to see if we can do anything else.
Thanks,
Jason
Hi All,
I tried a lot of Power Bi related fixes and none seemed to fix it.
I eventually moved all users timezones in D365 back to standard UTC and that solved the issue.
However, if Europe decides to get rid of daylight savings and stay on summertime all year then we will need to look at a bigger solution.
Jason
Community SupportHi, JayPow28
According to your description, if your date timezone in the Power BI Service is 1 hour behind, you can try to create a custom column in the Power query to change the UTC value of your time column, like this:
Custom= DateTime.AddZone([Time],1)Or
Custom= DateTime.AddZone([Time],-1)
For more method, you can refer to these cases:
https://community.powerbi.com/t5/Desktop/Change-time-zone-hours/m-p/640713
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-local-time-zone-in-DAX/m-p/43328
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/m-p/56337
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resolver IHi,
Thanks, I'm getting this error:
Expression.Error: We cannot convert the value #datetimezone(2021, 4, 23, 0, 0, 0, 1, 0) to type DateTime.
Details:
Value=23/04/2021 00:00:00 +01:00
Type=[Type]
I've tried changing the type of the column but it makes no difference.
Note that the [date] column that is giving me trouble is data from D365, it's not a column I created myself.
Each entry is a date stamp of the time entries:
See above, entries after March 28th are showing +01:00 and those before are 00:00
Regards,
Jason
- v-robertq-msft
Hi, JayPow28
According to your sample picture, I think you can try to check if it’s possible to replace the “+01:00” and “+00:00” with the blank value and change the column type to “Date/Time”
Here’s a solution that splits the columns and merges them again according to the requirement to get the date column, you can also follow it to hav a try:
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- JayPow28
Good suggestion, thanks I'll try it on a second column using a copy of the dates from D365.
I'll let you know what happens.
Thanks,
Jason