Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |