Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
alvin199
Helper III
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. 

alvin199_1-1644276201033.png

 

In Transformation Data page, I have filled the null value with 01/01/2021.

alvin199_0-1644276144494.png

 

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. 

alvin199_3-1644276817393.png

 

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

alvin199_4-1644277095803.png

 

If applied filter:

Total up value of each month in line chart = 136 (filter out Jan & Mar = 147)alvin199_5-1644277160266.png

 

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

 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community 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)
)

 

vluwangmsft_0-1644911255379.png

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

 

View solution in original post

8 REPLIES 8
v-luwang-msft
Community Support
Community 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)
)

 

vluwangmsft_0-1644911255379.png

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

 

TheoC
Super User
Super User

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

 

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!

Want to connect?www.linkedin.com/in/theoconias

Hi @TheoC 

 

This only solved for problem 1. 

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

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 🙂

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!

Want to connect?www.linkedin.com/in/theoconias

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

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

 

TheoC_0-1644288231300.png

 

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!

Want to connect?www.linkedin.com/in/theoconias

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

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. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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