Is there a better way to format when converting text in “dd/mm/yyyy” to Date in Power BI

Question

My data-source is an Excel file with some date in "dd/mm/yyyy" format and due to some error, read the date as text to Power BI. When I tried to convert it to a valid date using dax DATEVALUE(text) function, unfortunately these only converting to mm/dd/yyyy format.

I found that this can be converted to a date by splitting the text as below:

ATTENDANCE_DATE = DATE(RIGHT(ATTENDANCE[SERVICEDATE],4), MID(ATTENDANCE[SERVICEDATE],4,2),LEFT(ATTENDANCE[SERVICEDATE],2))

Still wonders if there any better way exits for date formatting in Power BI?

Answer 1

The best is to have the M query converted for you, i.e.

In case you're talking about how to display the date in a specific format, you should find the setting here:

Results: