Forum Discussion

tsuggs1's avatar
tsuggs1
Icon for Helper I rankHelper I
6 years ago
Solved

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

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...
Need Help
Tips and Tricks
  • az38's avatar
    az38
    6 years ago

    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's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

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

 

tsuggs1's avatar
tsuggs1
Icon for Helper I rankHelper I
6 years ago

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?