Forum Discussion
validate date using dax
yeah both months and days that are out of bounds of the month are treated like dateadd() which is not what i want
I think i figured this out yesterday - best way to check is to convert the parsed date back to a string in the expected format and make sure they match
Date Check Calc = VAR rawDate = MIN ( Table[RawData] ) RETURN SWITCH ( TRUE (), // Blank dates aren't an error but surface it differently to OK dates ISBLANK ( rawDate ), 2, // try to parse the date using hardcoded char locations. The date() parser is too forgiving - convert the date back to text and compare it to the raw value to make sure it hasn't been time travelled FORMAT ( IFERROR ( DATE ( LEFT ( rawDate, 4 ), MID ( rawDate, 6, 2 ), MID ( rawDate, 9, 2 ) ), BLANK () ), "YYYY-mm-dd" ) <> rawDate, 1, 0 )
My initial thought was that there are some "date" values from your source which are abnormal and don't exist in the calendar - If that's the case, I would suggest converting the value from the text directly and use IFERROR() function to handles errors.
Say, the source table looks something like this:
Date
2020-08-14
2020-08-33
2019-02-02
2019-02-30
Then we create a DAX measure like this:
Measure =
IFERROR(
IF(
HASONEVALUE('Table'[Date]),
DATEVALUE(VALUES('Table'[Date])),
"") ,
"Invalid Date")
With that, we will end up with a table like this:
| Date | Measure |
| 2020-08-14 | 2/2/2019 0:00 |
| 2020-08-33 | Invalid Date |
| 2019-02-02 | 8/14/2020 0:00 |
| 2019-02-30 | Invalid Date |
Does it help?