Date is shown incorrectly (DirectQuery)

[Jolyon]

Hello,
I have a question about import of Date values.
The matter is, we have imported data from Excel in CRM-Table, and then made a report in Power BI with Direct Query (I needed to present both CRM Data and Excel data; since Direct Query prohibits the use of any other data sources, we decided to import Excel data in a CRM-Table).
Now the problem is that the Date values are shown in Power BI incorrectly:
e.g. in Excel I have date 01.07.2016, in CRM it is shown also as 01.07.2016, but in Power BI - as 30.06.2016.

--> So the month begin is shown in Power BI as end date of previous month.

After checking the sql database we found out, that the date in the database is saved as e.g. 30.06.2016 (month end date), while in original data source(Excel) we have 1.07.2016.

What could be reason for the incorrect display of Date? How can it be, that CRM represents the Date correctly, even if Date in database is saved incorrectly? Thanks for the help! 

[ahipperson]

I have experienced the same issue and after some digging discovered that the date was being stored as a Date Time field, even though the user was just entering a date. The field was therefore saving the date as 01/07/2016 00:00:00. When I pulled the data through to Power BI I could see that somewhere along the line the value was being adjusted based on local settings, in my case it was converting GMT to British Summer Time which is one hour different, it therefore took an hour off the date, resulting in a value of 30/06/2016 23:00:00. When I then displayed the date it came out as 30/06 rather than 01/07.

I haven't got round to resolving this at source so in the short term I have created a caluclated column:

Table.AddColumn(#"Previous field", "Actual Date", each if DateTime.Time([Date]) < #time(12, 0, 0) then DateTime.Date([Date]) else Date.AddDays(DateTime.Date([Date]),1)),

[wojciech]

I have the same issue for some date values not all starngley enough, have you found a better solution since last time or you still use calculated column?

Thank you,

WJ

[v-qiuyu-msft]

Hi @Jolyon,

When connecting to SQL Server database in Power BI desktop, retrieved data are based on the records stored in the database. In your scenario, you need to make sure the data imported from Excel to SQL Server database are correct firstly. You can post a thread in SQL Server Database Engine forum.

By the way, why do you want to get data in DirectQuery mode? You can get data from CRM and Excel at the same .pbix file in Import mode.

Best Regards,
Qiuyun Yu