Forum Discussion
Convert date into fix format
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?
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
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.
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
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?