Forum Discussion

alvin199's avatar
alvin199
Helper III
4 years ago
Solved

Convert Text to Date with missing value

Hi,    I have a Text data type column called Commencement Date. It has missing value. I have 2 problems in here.    In Transformation Data page, I have filled the null value with 01/01/2021...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi alvin199 ,

    Try to adjust your dax to the below:

    Commencement Date_ = 
VAR mm = value(LEFT(MYWiT[COMMENCEMENT DATE], 2))
VAR dd = value(MID(MYWiT[COMMENCEMENT DATE], 4, 2))
VAR yy = value(left(year(NOW()),2)&""& RIGHT(MYWiT[COMMENCEMENT DATE], 2))

RETURN
IF((mm>12 || mm=1 || mm=3),
Date(yy, dd, mm),
Date(yy, mm, dd)
)

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

     

TheoC's avatar
TheoC
Community Champion
4 years ago

alvin199 I provided you something similar on another post but there was no response:  https://community.powerbi.com/t5/Desktop/Convert-date-into-fix-format/m-p/2319280#M838256

 

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:

 

 

The above screenshot and code is from the solution I put on your other post (it is still active as it's not marked as solved).  The above does exactly what you need in this instance as well as in the other instance.

 

Thanks,
Theo

alvin199's avatar
alvin199
Helper III
4 years ago

TheoC 

I did used the DAX you suggested and added 2 expression in the IF statement. I have mentioned it in my first post. Using it on the Date data type will result in the yyyy become 1921. 

I am not sure why.