Error with date function - calculated date column from text

[cathoms]

I have a column of dates in text format YYYMMDD and want to create a calculated column that converts those to date format. The following should work:

ApptCreationDate =
DATE(
     LEFT(Visits[AppointmentCreationDateKey],4),
     MID(Visits[AppointmentCreationDateKey],5,2),
     RIGHT(Visits[AppointmentCreationDateKey],2)
)

Unfortunately, there are some bad data in the column that need to be filtered out. I tried the following but it isn't working. I get a "Operator or expression'()' is not supported in this context. What am I doing wrong? 

ApptCreationDate = DATE(
    IF(Visits[AppointmentCreationDateKey]=-1, 
        FALSE(),
        (LEFT(Visits[AppointmentCreationDateKey],4),
        MID(Visits[AppointmentCreationDateKey],5,2),
        RIGHT(Visits[AppointmentCreationDateKey],2)
        )
    )
)

I also tried the following with the same result:

ApptCreationDate = IF(
    Visits[AppointmentCreationDateKey]=-1,
    BLANK(),
    DATE(
        (LEFT(Visits[AppointmentCreationDateKey],4),
        MID(Visits[AppointmentCreationDateKey],5,2),
        RIGHT(Visits[AppointmentCreationDateKey],2)
        )
    )
)

[parry2k · Accepted solution]

@cathoms try this, if there is error in conversion it will return blank() as catch-all errors otherwise return a date

ApptCreationDate = 
    IFERROR (  
        DATE 
        (
           LEFT(Visits[AppointmentCreationDateKey],4),
           MID(Visits[AppointmentCreationDateKey],5,2),
           RIGHT(Visits[AppointmentCreationDateKey],2)
        ), BLANK()
    )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[cathoms]

Thanks for the great help!

Cheers   - Christopher

[cathoms]

That works, but it is returning a column with a date time stamp. So the date is fine but they all indicate 12:00:00 AM. Documentation on the DATE function says it is returned as datetime. I don't want that visible so I changed the format to m/d/yyy in the Column Tools tab:

[cathoms]

There's some bad data in this column. Unfortunately I'm connecting to this dataset and can't transform it directly, so I'm not entirely sure what all the bad values are. Looks like there is at least a -1 and maybe a " instead of the text values...

So, while Pat's DAX allows me to create the column, it doesn't work in the table visual. Instead I get this:

[mahoneypat]

It would be much easier to do this in the query editor, but you need to wrap each of your text strings you parsed out with VALUE() to convert them to a number for the DATE function to use.

Date = DATE(VALUE(LEFT(DateText[DateText],2)), VALUE(MID(DateText[DateText],5,2)), VALUE(RIGHT(DateText[DateText],2)))

Pat

[PaulDBrown]

Please follow the recommendations in this thread to help us help you:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1964858#M7448... 

[parry2k]

@cathoms I guess it is a text column, try this;

ApptCreationDate = DATE(
    IF(Visits[AppointmentCreationDateKey]="-1", 
        BLANK(),
        (LEFT(Visits[AppointmentCreationDateKey],4),
        MID(Visits[AppointmentCreationDateKey],5,2),
        RIGHT(Visits[AppointmentCreationDateKey],2)
        )
    )
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡