Trying to find a way to exclude weekends using DateDiff with dates from two tables

[tsuggs1]

Hello,

I am hoping to find some clear advice on a way to find the number of days between two dates and exclude weekends.

I have tried some solutions I have seen where people have asked similar questions about excluding weekends or holidays, however my issue is somewhat unique as my dates are in two separate tables. I have Table 1 that contains a date column called 'ActualTime - CustomsCleared' and another table called Table 2 that contains a date column called 'ActualTime - Arrived'. I was able to create a relationship between the tables and use 'DateDiff' to calculate the number of days between with no issue.

I am struggling to find a way use this method with DATEDIFF that exlcludes weekends. Any help or advice is greatly appreciated!

[tsuggs1]

@az38 Here are the results, there are some empty rows.

[az38 · Accepted solution]

@tsuggs1 

try 🙂 but better to try to filter out values from shipmentlinks table that hav no relations

var _deliveredDate = RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered])
var _clearedDate = RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared])

RETURN
IF(ISBLANK(_deliveredDate) || ISBLANK(_clearedDate), 0,
datediff(RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered].[Date]), RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared].[Date]),DAY)
-
IF(
_deliveredDate < _clearedDate,
countrows(FILTER(calendar(_deliveredDate , _clearedDate), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7")),
countrows(FILTER(calendar(_clearedDate, _deliveredDate), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7"))
)
+1
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38]

@tsuggs1 

ok

var _deliveredDate = RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered])
var _clearedDate = RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared])

RETURN
IF(ISBLANK(_deliveredDate) || ISBLANK(_clearedDate), 0,
IF(
_deliveredDate < _clearedDate,
datediff(_deliveredDate, _clearedDate, DAY),
datediff(_clearedDate, _deliveredDate, DAY)
)
-
IF(
_deliveredDate < _clearedDate,
countrows(FILTER(calendar(_deliveredDate , _clearedDate), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7")),
countrows(FILTER(calendar(_clearedDate, _deliveredDate), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7"))
)
+1
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[tsuggs1]

It's still being funky..

It did change the value to '4', but not '-4'.

Overall there are various other examples where it only calculating a positive number where it should be negative:

Last thing, there seems to be '1' on dates that are equal, as seen here:

Tried to figure it out on my own, but no luck... It's definitely close than I have ever got.

[az38]

@tsuggs1 

hoe many days it should be between 10/25 and 10/30?

25oct - Fri

28oct- Mon

29 - Tue

30-Wed

totally- 4.

11/16 - 11/16 - it is the one day. 

if you need 3 days n the first case and 0days in the second- just remove "+1"from formula

var _deliveredDate = RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered])
var _clearedDate = RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared])

RETURN
IF(ISBLANK(_deliveredDate) || ISBLANK(_clearedDate), 0,
ABS(datediff(_deliveredDate, _clearedDate, DAY))
-
IF(
_deliveredDate < _clearedDate,
countrows(FILTER(calendar(_deliveredDate , _clearedDate), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7")),
countrows(FILTER(calendar(_clearedDate, _deliveredDate), FORMAT([Date],"w")="1" || FORMAT([Date],"w")="7"))
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[tsuggs1]

@az38  You're right my count was off and this is working to calculate the days between the dates positively. 

So is there no way to determine the date difference with the negative number using this formula as the DATEDIFF does?

The issue is I have another measure that counts anything in "Days Between" less than 2 as on time. So where as the example in this screenshot would be considered on time, if I use the newly generated "Days Between" that excludes weekends since it's a positive 10 it would look like it's actually 10 days rather than being early (if that makes sense).

Maybe that step is something I can tinker around with to fullfill what I need to show.. I grealty appreciatie all of your assistance! 

[az38]

@tsuggs1 

to get positive value of datediff you should place earlier date as first input option like datediff(mindate, maxDate, DAY)or just use ABS() function like ABS(Datediff(mindate, maxDate, DAY))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[tsuggs1]

@az38 To clarify, this is always going to make the result number a postitive number of days between? 

[az38]

@tsuggs1 

datediff(date1, date2, day) always returns day2-day1

abs(datediff(date1, date2, day)) always return positive value

do not hesitate to give a kudo to useful posts and mark solutions as solution

[tsuggs1]

@az38 Okay so it is calculating. I tried manipulating it, but the number just doesn't seem right i.e. in the second row the dates are 10/25/2019 - 10/30/2019 which is -5 in the days between. There is a weekend between these two dates, however the result in Column 3 (using the formula you gave me) is calculating -6. The proper calculation would result in -4 as it would not count the 2 weekend days. It's so close..

I tried manipulating the end of the formula you send me, but it's still giving me a random number.

When you say filter out values that aren't used in the relation are you referring to the blanks or the actual columns that aren't being used?