cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vcmoffatt Frequent Visitor
Frequent Visitor

Microsoft Dynamics CRM dates incorrect in Powerbi

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

1 ACCEPTED SOLUTION

Accepted Solutions
deldersveld
Advisor

Re: 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

4 REPLIES 4
deldersveld
Advisor

Re: 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

Highlighted
Community Support Team
Community Support Team

Re: Microsoft Dynamics CRM dates incorrect in Powerbi

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vcmoffatt Frequent Visitor
Frequent Visitor

Re: Microsoft Dynamics CRM dates incorrect in Powerbi

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/

Mezmarianne Regular Visitor
Regular Visitor

Re: Microsoft Dynamics CRM dates incorrect in Powerbi

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.