Forum Discussion
DAX issue with Date data type
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.
That's a real zinger.
It took me a little while to reproduce. I can reliably get that behavior for any date < 1899-12-30. That is the 0-date in the Tabular engine, and my guess is that someone didn't handle negative values appropriately in the function definition. I can bounce up against the upper date boundary without errors.
Do you have data with dates < 1899-12-30?
**Edit:**
Power Query does not suffer from a similar error, and Date.WeekOfYear() is functional in the full range from 0001-01-01 through 9999-12-31. DAX Can't handle dates as small as 0001-01-01, so you should be good to do this sort of transformation in Power Query. Generally it's a better practice to do transformations before importing to the data model, anyway, to take advantage of better compression.
**Edit2:**
The error reported in the original post seems to be unique to <1899-12-30 dates. If I input malformed dates or text strings I get
Cannot convert value '4' of type Text to type Date.
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.