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

 

I have already created a column in my date table which identifies a working day as 1 and a non working day as 0

 

I would like to create a calculated column to show the number of working days. If the onsite date and completion date are on the same day then the output would need to show 1.

Onsite_DateCompletion DateWorking days
10-Sep-2114-Sep-213
10-Aug-2111-Aug-212
10-Aug-2111-Aug-212
10-Aug-2111-Aug-212
10-Aug-2111-Aug-212
10-Aug-2111-Aug-212
10-Aug-2111-Aug-212
10-Aug-2111-Aug-212
17-Aug-2123-Aug-215
10-Aug-2111-Aug-212
11-Aug-2123-Aug-219
11-Aug-2123-Aug-219
11-Aug-2123-Aug-219

 

Thank you in advance

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

4 Replies

  • 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))

        )

  • KG1's avatar
    KG1
    Resolver I
    4 years ago

    Hi - there were anomilies in there data where some the of the end dates were before the start dates. I created 2 new conditonal columns to flip the dates around and the DAX worked perfectly - thank you very much