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

v-yulgu-msft Super Contributor
Super Contributor

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/

Highlighted
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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 293 members 3,274 guests
Please welcome our newest community members: