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
    )

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I am not sure why my previous reply to this post was lost ... I will try submitting again

 

My initial thought was that there are some "date" values from your source which were abnormal and doesn'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:

 

 

Then we create a measure like this:

 

Measure = IFERROR(
            IF(HASONEVALUE('Table'[Date]),
                DATEVALUE(VALUES('Table'[Date])),
                ""),
                "")

 

 

We will end up with something like this:

 

 

Hope this helps

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)