Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
KG1
Resolver I
Resolver I

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

4 REPLIES 4
KG1
Resolver I
Resolver I

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
Super User
Super User

@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

Hi Apologies but I get the following error message

 

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

 

 

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

)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.