Forum Discussion
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 RegardsLucien
8 Replies
- v-luwang-msftCommunity 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 RegardsLucien
- TheoCCommunity 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