Forum Discussion

granthworth's avatar
granthworth
Icon for Kudo Kingpin rankKudo Kingpin
10 years ago
Solved

DAX issue with Date data type

I am having an issue today with a DAX formula.   The scenario is as follows. I have 2 Power BI desktop files which are formatted identically. I have them linked to separate Excel workbooks which ar...
  • granthworth's avatar
    granthworth
    10 years ago

    Thanks for the input everyone.

     

    However, after going through my source data, I found the following string of typos in the date column. Correcting them also corrected this issue.

     

    381	1/19/16
382	1/19/16
383	1/19/16
384	1/19/16
385	1/19/16
386	1/19/16
387	01/119
388	01/119
389	01/119
390	01/119

    I have adjusted the data validation and formatting restrictions to the user form through which this data is input.

     

    Thanks again for helping with my case of the Mondays.

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago
Huh, if it is a pre-1900 date issue, I guess I should have thought of that, I just posted an article on it in the Community Blog! :)
greggyb's avatar
greggyb
Icon for Resident Rockstar rankResident Rockstar
10 years ago

Greg_Deckler, the 0-date in Tabular is 1899-12-30, not 1900-01-01. This is actually inconsistent with both Excel and SQL Server.

 

As you can see below, I've created a table with the integer values -1, 0, and 1. I then change the data type to Date, and I get back 1899-12-29, 1899-12-30, and 1899-12-31. The misbehavior starts for anything with a negative offset from the 0-date.

 

First integersIntegers converted to Date datatype in PBI