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 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?"

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
    )

10 Replies

Greg_Deckler
Community Champion
5 years ago
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
  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
    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...
Anonymous
Not applicable
5 years ago
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?
amitchandak
Super User
5 years ago
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
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
  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
    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)