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
    )

     

Anonymous's avatar
Anonymous
Not applicable
5 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
5 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
    5 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)

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I got your point. The reason why 2021-02-08 when the data fed in was 2020-14-08 was that the Date() function would add the month to year if the integer representing the month is over 12 ...

     

    Per my understanding, what you are looking for is how to validate the data before you process the data - like you said you would like to extract date part from the source (but what would you like to do if you found the date is invalid? Like the 2020-14-08 case?) Then this is one way to look at it:

    Measure = VAR Input = VALUES('Table'[Date])
            VAR _position_1 = FIND("-",Input)
            VAR _position_2 = FIND("-",Input,_position_1 + 1)
            VAR _year = LEFT(Input,_position_1-1)
            VAR _month = MID(Input,_position_1+1,_position_2 - _position_1 - 1)
            VAR _day = RIGHT(Input,LEN(Input)-_position_2)
            VAR _date = DATE(_year,_month,_day)
        RETURN IF(
                HASONEVALUE('Table'[Date]),
                    IF(FORMAT(_date,"yyyy-mm-dd") == Input,Input,"error message ... or something you would like to do with the invalide date"),
                "error message ...")

    In brief, I break down the input date into dateparts, and then use the Date() function to generate a new date. If these two are identical then we assume that the input string is in correct format ... then you can go ahead with the verified format.

    The point is ... what would you like to do with the date in invalide format (for example, 2020-14-08)? I think there is very little that we can do ...... 

    • jakubk's avatar
      jakubk
      Helper I
      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
    )