Inaccuracy in Date Fields with Microsoft Dynamics 365 import

[PBandI]

I am trying to understand what may be happening when importing data from Microsoft Dynamics 365 when it comes to dates. We use dynamics 365 as our primary CRM tool and import the necessary fields into Power BI in order to analyze and report on certain topics. One of the tables that we import is the Opportunity table. As you can see in the screenshot below, we have a field where a user can input a delivery date in this opportunity:

When I import this table and utilize the necessary fields in Power BI, I see that the date is reported incorrectly for these records. It seems to always be one day in the future of what was actually input. When I explore the Table view you can see this error:
(don't worry about the name of the column/field - they are the same)

I feel like it could be a time zone issue between the two systems, but I still wouldn't know how to solve that problem. If anyone could help me understand what may be happening or how I can resolve this issue that would be greatly appreciated.

[PBandI]

@mostho @christinepayton thank you for the tips. To make matters worse/weirder, I adjusted the date in Dynamics 365 to 9/29/2024 to make sure that this record appeared in the correct month, and now it reports 9/29/2024 correctly without adding a day in Power BI. It is very strange as it seems to only apply this addition of a day to dates that are on the last day of the month. I am very confused. 

[christinepayton]

🤣 Shane Young did a really good video on how Dataverse handles date time fields if you want to search that on YouTube. It can be set to either use local user time or a standardized time, I'm not sure which yours is set to... but I thiiiink it gets stored in UTC either way if I had to guess, because databases tend to do that. I think apps end up re-converting it back to local user time on the front end when they show it to a given user. 

[PBandI]

Do you by chance have the link for the referenced video?

[christinepayton]

I think it was this one! I thought it had something about the dates in the title but apparently not: 
https://www.youtube.com/watch?v=kxxEC1xH9sI&list=PLCGGtLsUjhm3BSR2bCI_G5LAbcXLKmPm3&index=1&t=803s

[christinepayton]

It's probably midnight UTC if I had to guess, SharePoint does this too. You can use AddHours to shift it, or look at the Dataverse column settings as I believe there's some handling of how the timestamp is stored there. 

[mostho]

I have a similar issue with importing dates from sharepoint lists.in this case its espacially annoying as we do not enter a time in the sharepoint list, but sharepoint "adds" a time.

From my point of viiew it looks like the time is stored in a (defined) timezone but this timezone is not part of the datafield. so I need to know this timezone (e.g.) and adjust the date/time at the import.

Maybe this helps you for your issue.