Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Jolyon
Helper III
Helper III

Date is shown incorrectly (DirectQuery)

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!

3 REPLIES 3
ahipperson
Frequent Visitor

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)),

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
Community Support
Community Support

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors