Microsoft Dynamics CRM dates incorrect in Powerbi

[vcmoffatt]

Hi,

We record all our project information in CRM and record the Sales Date for each project. This is obvioulsy key for reporting on sales figures. However I have noticed that the dates in Powerbi are not as they should be. It looks like the Powerbi dates are a day behind for some records. For example if the date in CRM is 1/4/17, it is showing in Powerbi as 31/3/17. However this is not occuring for all records, some are showing with the correct dates.

Any ideas as to why this is occuring and how to resolve this issue??

Any help gratefully received.

thanks

[v-yulgu-msft]

Hi @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

[vcmoffatt]

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/

[Anonymous]

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.

[deldersveld · Accepted solution]

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