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

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

     

30 Replies

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

    Hi tsuggs1 

    try this technique. create a calculated column

     

    Column = datediff([StartDate];[EndDate];day)-countrows(FILTER(calendar([StartDate];[EndDate]);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

      Hi az38,

       

      Not sure what I'm doing wrong, but it's throwing an error on everything past 'FORMAT'.

       

      See attached screenshot.

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

        tsuggs1 

        replace ";" to comma ","

         

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