How to convert a date by using DAX functions?

Question

I'm working on a PowerPivot project, and I need to convert a date to another format. My SQL Server Analysis Cube provides me a Time dimension including a date attribute. I want to convert it to a dd/mm/yyyy format to create a reference to another data source (Excel file).

I tried to convert it by using standard DAX date functions but it's not recognized as a date, it's seems it is due to the name of the day added as a prefix. How can I transform it to the dd/mm/yyyy format ? How to extract the sub string after the comma ?

Thanks !

Answer 1

I used the following data to test my solution out.

You can use the SEARCH function to find the first instance of a string. So I can parse just the date portion out with the following formula:

=right([Datefield],(LEN([Datefield])-SEARCH(",",[Datefield])-1))

It gets the substring starting at the character after the comma through the end of the string.

The DATEVALUE function takes a string that represents a date and turns it into a date. I can combine that with my previous function:

=datevalue(right([Datefield],(LEN([Datefield])-SEARCH(",",[Datefield])-1)))

In the picture below, the first column is the original data. The second column is the function that parses out the substring for the date. The third column takes the datevalue of that date string in the second column. The fourth column is the all in one formula with both the substring and the datevalue.