Forum Discussion

alvin199's avatar
alvin199
Helper III
4 years ago
Solved

Convert date into fix format

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...
Need Help
  • alvin199's avatar
    alvin199
    4 years ago

    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?

amitchandak's avatar
amitchandak
Super User
4 years ago

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's avatar
alvin199
Helper III
4 years ago

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