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