Forum Discussion

jakubk's avatar
jakubk
Helper I
5 years ago
Solved

validate date using dax

i have a live connection i need to ID which rows contain invalid dates in a particular string column. the date format should be YYYY-mm-dd I can't do it at the source query   I thought i had it w...
  • jakubk's avatar
    jakubk
    5 years ago

    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
    )

     

jakubk's avatar
jakubk
Helper I
5 years ago

Lost my reply to a "unexpected error occured, here goes again"

 

that'll work for month, but what about today's dax friendly date? february 207th?

 

 

My source is a ERP with a custom column added - unfortunately all custom defined columns are varchar with no format mask constraints, so sometimes transposed dates get through

 

I ended up splitting off the date() check and using format(iferror(col,blank()),"YYYY-mm-dd") <> col to convert ~valid~ dates back to my original format and compare it to the original value to make sure it hasn't been time travelled. Bleh, i hate being made to write convoluted code

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

jakubk - Yeah, maybe DAX is better, you could extract the Month by text parsing and you could check the End of Month scenario using EOMONTH maybe. No offense but that sounds like one jankey ERP system...