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

     

alvin199's avatar
alvin199
Helper III
4 years ago

Hi TheoC 

 

This only solved for problem 1. 

For Problem 2, the DAX cannot used on Date data type. 

TheoC's avatar
TheoC
Community Champion
4 years ago

Hi alvin199 

 

Now that you have solved the primary issue, you likely don't need to create new calculated columns to generate the date anymore because the Date is now corrected in the first instance.

 

Hope that makes sense?

Theo 🙂