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 

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'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