validate date using dax

[jakubk]

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 with date() but i was caught out when it let 2020-14-08 though. Apparently this is 2021-02-08??? I'm not sure I've ever seen that calendar. I used to using dateadd for that kind of functionality rather than overloading the month and day components...

suggestions?

My current code is

Date Check Calc2 = 
// need to create as a measure because dax functions on live connections are nerfed
var rawDate = min(Table[Date_Col])
return switch(true(), 
isblank(rawDate),2,
isERROR(date(LEFT(rawDate,4),MID(rawDate,6,2),MID(rawDate,9,2))) || MID(rawDate,5,1) <> "-" || MID(rawDate,8,1) <> "-" ,1,
0)

I already feel it's too long for a question so simple. "Can you make this a date based on this format mask?"

[Anonymous]

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]

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)

[Anonymous]

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 · Accepted solution]

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
    )

[Greg_Deckler]

@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)

[amitchandak]

@jakubk , what is wring data expected. One check

if(len([col] <12, blank(), [col])

Now this table can be marked as date in power query with igonre error.

Can you share sample data and

[Anonymous]

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?

[Greg_Deckler]

@jakubk - Probably could think of something in DAX but maybe just duplicate the column, split out the column in Power Query, check if the middle value is <=12?

Not sure what sorts of problems you are seeing in your data.

[jakubk]

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]

@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...