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

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
    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 🙂

      • alvin199's avatar
        alvin199
        Helper III
        4 years ago

        TheoC 

         

        The reason to create a calculated column is to correct the Commencement Date that contain mixture of month and day in the first 2 digit of that column. 

        However, the DAX to create the calculated column is not perfect to correct the month and day that has "01" as it is 1st January. There are few rows in the processed Commencement Date_ column.

         

        Since the DAX contain January month, if I use untick it in the Filter Panel, the total of the individual value of each month will not be the same as display in the Title.