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

 

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

7 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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
  • parry2k's avatar
    parry2k
    Super User
    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

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

      Thanks for the great help!

       

      Cheers   - Christopher

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

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

  • 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: