Forum Discussion
Microsoft Dynamics CRM dates incorrect in Powerbi
It could be a Time Zone/UTC issue. There are several related functions you could use in Power Query when you load the data to try to resolve the issue if that's the cause: https://msdn.microsoft.com/en-us/library/mt296609.aspx
Microsoft EmployeeHi vcmoffatt,
This behavior might be related to the Time Zone/UTC settings. You can use DateTimeZone.SwitchZone function or DateTime.AddZone function to modify the utc timezone to your local timezone.
Alternatively, you can create a new custom column which takes account of the difference of time values between CRM and local machine. That is to say minus the time difference when loading data from CRM in Power Query.
Here are some similar threads for your reference:
Date is shown incorrectly (DirectQuery)
CRM API REST date time zone UTC issue
Regards,
Yuliana Gu
Thank you both v-yulgu-msft and deldersveld, for putting me in the right direction. I now understand that Microsoft Dynamics CRM stores all dates as UTC - even though it displays it in local time, so you were correct. I have now resolved the issue I used
DateTimeZone.ToLocal to convert to local time following the guide below.
http://scaleablesolutions.com/convert-utc-date-format-into-local-time-format-in-power-bi/
Thank you for linking to this solution. I had all dates off by 1 hour - enough to put them into the wrong month and year.
