Difference between Dynamics 365 and Azure SQL data

[Anonymous]

Dear reader(s),

I'm currently working on a dashboard in Power BI regarding our company-wide time-entry which is standardly entered in Dynamics 365. I have access to this data through the Azure SQL's DirectQuery option.

There is but one problem which I cannot explain: I've made a visual (chart) in which I can see the weekly amount of total worked hours. However, there seems to be a shift in weekdays: hours which are truly made on a monday (according to Dynamics 365) are displayed as sunday-hours (note: we do not work on sundays) in my visual (this data is also incorrectly recorded in my Power Query).

I thought there could be problems with my settings but I can't figure it out. 

Hoping someone is able to help, feel free to ask for any additional information!

Kind regards, Thomas

[Greg_Deckler · Accepted solution]

@Anonymous - OK, whew! I thought maybe the world had gone crazy there for a second. I haven't heard of anyone getting access to Dynamics 365 base SQL tables. Well, the first thing I would check would be to check the time zone settings for your Dynamics 365 instance and your Azure SQL Server instance and, heck, your Power BI instance for good meausre. Make sure they are all the same. I suppose that having different time zones could affect this kind of thing. The only other thing I can think of is if there is a difference in the base dates used by Dynamics 365 and Azure SQL Database but I can't imagine that is the case. There is a difference in the base reference date between SQL Server and Power BI. Power BI is midnight Dec 30 1899 if I recall correctly. I believe SQL Server is 1/1/1900

[Anonymous]

@Greg_Deckler

My apologies for the delayed response! Since I'm new to this forum I had some trouble logging in..

Thank you very much for your quick answer, I'm going to contact our chief IT-manager with this information and hopefully we'll manage to fix it. Since the end of the year is approaching, he doesn't seem to be in office as soon as begin january.

I'll keep you updated! Thanks!

[Greg_Deckler]

@Anonymous - So, you have direct access to the Dynamics 365 core databases? Or is this data entered into Dynamics 365 that is being written to a separate Azure SQL Database? 

[Anonymous]

@Greg_Deckler

The second is the case here. My colleagues enter the data into Dynamics 365. This data is transferred to an Azure SQL database that I have connected to my Power BI file.

In short, the problem is that there is a difference between my data in Dynamics and the database I use for Power BI. An example: I entered in Dynamics that I worked for eight hours yesterday (monday 28-12). My Power BI data model displays this same data into 27-12, which means that my data is not reliable.

[parry2k]

@Anonymous I don't think it is PQ or Power BI issue, you need to ask/find out Dynamics 365 how it stores the data? What timezone? Is it in UTC or what? Assuming it is in UTC, you probably need to change your timezone to get the proper results. Again, it is more of a Dynamics question.