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

ok, tsuggs1 

lets try to debug.

add

column=RELATED('scope_live_scope_gws_riege_com ot_milestone (2)'[actualTime -Delivered].[Date])

and

column2=RELATED('scope_live_scope_gws_riege_com ot_milestone'[actualTime - Customs Cleared].[Date])

to your shipment_links table.

then go to the Data view (left pane of power bi window) and in table check there will not be any empty values in column and column2

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

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

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? 

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

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.

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

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! 

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

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

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