Forum Discussion

Jolyon's avatar
Jolyon
Icon for Helper III rankHelper III
9 years ago

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 C...
Need Help
ahipperson's avatar
ahipperson
Frequent Visitor
9 years ago

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's avatar
    wojciech
    Icon for Helper II rankHelper II
    3 years ago

    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