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

     

v-luwang-msft's avatar
v-luwang-msft
Community Support
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