Forum Discussion

KG1's avatar
KG1
Resolver I
4 years ago
Solved

Working Days

Hi    I have a date table linked to a completion date in my data table.   I need to be able to calculate the number of working days between the onsite date and completion date in the data table ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    KG1 , Try a new column like

     

    business Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Onsite_Date],Table[Completion Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

     

    How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

amitchandak's avatar
amitchandak
Super User
4 years ago

KG1 , Try a new column like

 

business Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Onsite_Date],Table[Completion Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

 

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

  • KG1's avatar
    KG1
    Resolver I
    4 years ago

    Hi Apologies but I get the following error message

     

    The start date in Calendar function can not be later than the end date.

     

     

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      KG1 , Make sure the first date is a smaller one

       

       

      business Day = if(Table[Onsite_Date] < Table[Completion Date] , COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Onsite_Date],Table[Completion Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)),
      COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Completion Date],Table[Onsite_Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

      )