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

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. 
 

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.