Forum Discussion

cathoms's avatar
cathoms
Responsive Resident
5 years ago
Solved

Error with date function - calculated date column from text

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...
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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's avatar
cathoms
Responsive Resident
5 years ago

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: