Convert date into fix format

[alvin199]

I have a date column like below:

The format is mix up. For example, the correct format should be DD.MM.YYYY. Row 1-3 are correct but 4th row is showing differnet format MM.DD.YYYY. 

How can I convert the the 4th row to DD.MM.YYYY?

[TheoC]

Hi @alvin199 

If you don't manage to get the other solutions to work, you can create a Calculated Column as follows:

Date Correction = 

VAR _DD = VALUE ( LEFT ( 'Table'[Date] , 2 ) )
VAR _MM = VALUE ( MID ( 'Table'[Date] , 4 , 2 ) )
VAR _YYYY = VALUE ( RIGHT ( 'Table'[Date] , 4 ) )

RETURN

IF ( _MM > 12 , DATE ( _YYYY , _DD , _MM ) , DATE ( _YYYY , _MM , _DD ) )

Outputs per below:

Basically, the above formula checks the middle to determine if the month is greater than 12 which creates a switch.  The only issue you will have is that if you have a large dataset and there is many of these date errors, it's likely that irrespective of what approach you take, there may be the situation that arises where you have 11/3/2022 which may be 3 November 2022 or 11 March 2022.  Please take caution to this.

All the best.

Theo

[alvin199]

Hi @TheoC 

Thank you for the suggestion. 

I have 1 point not understand. In line 9, the Then and Else statement does not reflect in the output. This means the Column value does not start with YYYY.

[TheoC]

@alvin199 the THEN / ELSE uses DATE which to format the text to a date. DATE function is used YYYY , MM ,DD but translates to DD/MM/YYYY or aligns to Date format in Power BI.

Hope that makes sense. Please test the formula.

Thank you.

Theo

[alvin199 · Accepted solution]

Thanks for your explanation. The IF Then statement is how the year, month and day sequence based on DATE formula and the output display is based on our compute setting. 

As you have raised caution regarding the display result may be have conflict. For example, 02/12/2022 (text) can be 2nd December 2022 but actual should be 12nd February 2022, especially there are many rows with different date format in single column. 

Thus, the suggested DAX cannot handle it effectively. What we can do to solve this besides mannually change it in Excel?

[TheoC]

@alvin199 DAX does not do miracles and it cannot "guess" which rows in your source file are meant to be days or months, especially when there is a chance that they can be either or.  The only way that you can sort this out is by cleaning up the raw data file.

[alvin199]

@TheoC 

Alright. Thanks for the advise. 

It is a good practise for me to think, act and learn from you. 

Really appreciate your responses. 

[amitchandak]

@alvin199 , first of all, has power bi taken it has date. Then you can use the format of your choice using the format option.

I doubt it is still text or power bi has not taken it in the current format

refer

DD/MM/YYYY to MM/DD/YYYY
date(year( right(DD__MM__YY[date],4)), month(mid(DD__MM__YY[date],4,2)) ,day(left(DD__MM__YY[date],2)))

MM/DD/YYYY to DD/MM/YYYY
date(year( right(DD__MM__YY[date],4)), month(left(DD__MM__YY[date],2)),day(mid(DD__MM__YY[Version_Id],4,2)) )

[alvin199]

I am able to extract the date for the first 3 row but it display weird result on the 4th row. 

If need to use IF Then statement, not sure how to incorporate it correctly 

[alvin199]

@amitchandak 

I encounter errror. 

The original data type for Date column is text by Power BI. 

Also, using this formula will create error for the 4th row as its format is not the same with the first 3 row.