Forum Discussion

jakubk's avatar
jakubk
Helper I
6 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
    6 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
6 years ago

yeh, i got an email that you replied (and one in spanish too???), but then didn't see it in the thread

 

i need to constrain it to YYYY-mm-dd format because I need to capture transposed days and months. Seems like datevalue() will keep trying different format masks until it finds one that works (12th of jan becomes 1st of dec)

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

jakubk - Actually seems like it could potentially be much more complex than what you have today. You could parse out the month and day and year via DAX MID function, RIGHT function and probably FIND/SEARCH possibly coupled with SUBSTITUTE. You might need to implement logic to determine leap year status - https://community.powerbi.com/t5/Quick-Measures-Gallery/Leap-Year/m-p/442398#M159 but probably not.

 

Now you would be in a position to determine if month > 12 as well as whether days exceeded the number of days in a month, 

 

  IF(DAY(EOMONTH(DATE(__year, __month, 1),0) > __day, FAIL!!, pass)