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.

 

Then I apply DAX to make the first 2 digit if more than 12 and equal to 1 then it is a day (the data collector mix the first 2 digit with month & day so impossible to have month in January & more than 12), otherwise it is a month. 

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

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

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

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

 

 

Problem 1:

However, after appled te DAX above, the year become 1921. 

 

Problem 2:

The DAX is still unable to remove date with 01/01/yyyy due to year and month is the same value. If I filter out 01/01/yyy in the Filter Panel of line chart, the total showing in the Tile with reflect different value with the value of each month in the line chart. 

 

Before apply filter

Total up value of each month in line chart = 283

 

If applied filter:

Total up value of each month in line chart = 136 (filter out Jan & Mar = 147)

 

The total in the tile is correct. I will maintain it but just having the 2 problems as mentioned above.

 

Here is the PBIX that have my problems.

https://drive.google.com/file/d/1ORCFVvbqLaYArkqIZBtgHSPws0P9R8Uh/view?usp=sharing

 

  • 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

     

8 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support

    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

    Hi alvin199 

     

    Go to the step "In Transformation Data page, I have filled the null value with 01/01/2021." Change 01/01/2021 to "01/01/21".

     

    Then convert the column to Date type.

     

    This should make all records in the column the same and then fix the issue.

     

    Let me know if it doesn't.

    Theo

     

    • alvin199's avatar
      alvin199
      Helper III

      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

        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 🙂