ERROR in changing date type from text to date

[LucienF38]

Dear all,

I have an issue converting my date from text format to date format. The thing is:

- I had my date in the following form :PIC 1

- I changed it with the following formula and obtained the following result : 

RGDT- Entry Date (french) = RIGHT('OOLINE : CO line file (OB)'[RGDT - Entry date]; 2) & "/" & RIGHT(LEFT('OOLINE : CO line file (OB)'[RGDT - Entry date] ; 6);2) & "/" & LEFT('OOLINE : CO line file (OB)'[RGDT - Entry date] ; 4 )pic 2

- I did this for 2 different columns, Entry Date and Purchase Order Date. Now I want to change their formats to type date but it comes the following error message. How can I avoid this ? 

Second issue : From these 2 columns I want to do a difference to know if the orders has been entered in our system within 2 days. However, the problem is the non-working days (Weekends).  Can you propose a methodology to reach this goal please ? The need is to do not take into account these non-working days in the 48h hours goal. 

Thanks for helping 🙂

[HotChilli]

It looks like you have a blank value or space or similar in your data

[LucienF38]

Dear HotChili,

You're right there are missing values. Actually the fact is that I have imported a column with the function RELATED from another table. Therefore, a very few number of rows are not matching (120 rows out of 60K). But it is a problem for me because i can't convert my data into date format because of those. Do you have any idea on how I can delete these rows (not a big loss for me...) ? Or any alternative method ? 
I've already tried to play on the filters and relationships...

Thanks for your help.

[HotChilli]

Well, the usual method is to use Power Query to clean up the data before it gets in to the model.  Is that possible?

[LucienF38]

Thanks guys ! I found the issue with taking off every single filter in power query !

I don't close the subject because i still need to see the second point. 🙂 🙂

[v-juanli-msft · Accepted solution]

Hi @LucienF38 

For second point, create columns

Column-entry date = LEFT([entry date],4)&"/"&MID([entry date],5,2)&"/"&RIGHT([entry date],2)

Column-order date = LEFT([purchase order date],4)&"/"&MID([purchase order date],5,2)&"/"&RIGHT([purchase order date],2)

week diff = DATEDIFF([Column-entry date],[Column-order date],WEEK)

day-order = WEEKDAY([Column-order date],2)

day-entry = WEEKDAY([Column-entry date],2)

day diff = IF([week diff]=0,[day-order]-[day-entry],IF([week diff]>0,ABS([day-order]-[day-entry])))

flag = IF([week diff]=0,IF([day diff]<=2,"within 2 days"),IF([week diff]>0,IF([day diff]<=4,"within 2 days")))

Or you could only create a measure

Measure 2 = var week_diff = DATEDIFF(MAX([Column-entry date]),MAX([Column-order date]),WEEK)

var day_entry = WEEKDAY(MAX([Column-entry date]),2)

var day_order = WEEKDAY(MAX([Column-order date]),2)

var day_diff = IF(week_diff=0,day_order-day_entry,IF(week_diff>0,ABS(day_order-day_entry)))

return IF(week_diff=0,IF(day_diff<=2,"within 2 days"),IF(week_diff>0,IF(day_diff<=4,"within 2 days")))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ntaylo06]

A possible low tech solution to the  "//" problem: Encase your logic in a simple IF(ISBLANK(), , ) if statement.

IF(ISBLANK([LMDT - Change Date]), "01/01/1901", rest of formula here)

You would then know that any rows with "01/01/1901" were blank.

[HotChilli]

..and for the second part.

https://www.sqlbi.com/articles/counting-working-days-in-dax/

You need a Date table and a 'isWorkingDay' column first

[LucienF38]

Thanks for replying. For the first part, you are right. This is the issue. Now I'm trying to get rid of these blank values. 

For the second issue, I'll look at it an reply later 🙂