Convert Text to Date with missing value

[alvin199]

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

[v-luwang-msft · Accepted solution]

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]

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]

Hi @TheoC 

This only solved for problem 1. 

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

[TheoC]

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]

@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]

@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]

@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.

[alvin199]

Filtering out the Jan & March value are not the correct way as the team has recorded 283 job placement. 

The problem is the data collector mix up the date & month. It is suppose not having any data recorded from Jan-March.