Working Days

[KG1]

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_Date	Completion Date	Working days
10-Sep-21	14-Sep-21	3
10-Aug-21	11-Aug-21	2
10-Aug-21	11-Aug-21	2
10-Aug-21	11-Aug-21	2
10-Aug-21	11-Aug-21	2
10-Aug-21	11-Aug-21	2
10-Aug-21	11-Aug-21	2
10-Aug-21	11-Aug-21	2
17-Aug-21	23-Aug-21	5
10-Aug-21	11-Aug-21	2
11-Aug-21	23-Aug-21	9
11-Aug-21	23-Aug-21	9
11-Aug-21	23-Aug-21	9

Thank you in advance

[KG1]

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

[amitchandak · Accepted solution]

@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]

Hi Apologies but I get the following error message

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

[amitchandak]

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

)