cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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

1 ACCEPTED SOLUTION
Community Support

Hi @alvin199 ,

``````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)
)``````

Best Regards

Lucien

8 REPLIES 8
Community Support

Hi @alvin199 ,

``````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)
)``````

Best Regards

Lucien

Super User

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Helper III

Hi @TheoC

This only solved for problem 1.

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

Super User

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 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Helper III

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.

Super User

@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 ) )RETURNIF ( _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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Helper III

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

Helper III

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors