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

     

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

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

az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

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