Forum Discussion

StuartSmith's avatar
StuartSmith
Icon for Power Participant rankPower Participant
2 years ago

Count Business days from Date

How can I find the n'th +1 business date from a date?

 

Calculated Column =

VAR AbsenceStartDate = 'Table: Absence Recording'[2) Absence Start Date] //This is a date

VAR BackfillRequiredWithinDays = CALCULATE(MIN('Table: Absence Master List'[12) Backfilled Required within Days])) //This is a number
Return
...
 
So if "AbsentDate" is "(Tuesday) 23/01/2024" and "BackfillRequiredWithinDays" is "5", I want to get the 6th Business day from "(Tuesday) 23/01/2024"
 
Hope that makes sence & thanks in advance.

 

Need Help

5 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    2 years ago

    Do you only wish to count work days ?

    (i.e. exclude weekends and holidays) 

    • StuartSmith's avatar
      StuartSmith
      Icon for Power Participant rankPower Participant
      2 years ago

      Yep, just monday to fridays.  Was hoping that I could write something like "Networkdays(AbsenceStartDate  + BackfillRequiredWithinDays + 1) 😁, but NetworkDays doesnt work like that 

      • StuartSmith's avatar
        StuartSmith
        Icon for Power Participant rankPower Participant
        2 years ago

        Or taking a different approach, but getting to the same end result.  I have a table similar to the below...

        So I want to count down the rows for each ID by the value in Required by

         

        ID AbsecnceDate RequiredBy Result   
        1 03/01/2024 4 08/01/2024   
        1 04/01/2024 4 08/01/2024   
        1 05/01/2034 4 08/01/2024   
        1 08/01/2024 4 08/01/2024   
        1 09/01/2024 4 08/01/2024   
        2 03/01/2024 2 04/01/2024   
        2 04/01/2024 2 04/01/2024   
        2 05/01/2024 2 04/01/2024   

         

        Hope that makes sence and probably easier as the dates are already weekdays

         

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    2 years ago

    So ok to include national holiday  ... like Labour day and Christmas?

     

    As you can imaging the formulawill be a lot more complicated becaiue each country has different holidays each year !