Networking Days


#1

I have a table with reporting period start date and end dates and I need to calculate the number of working days (excluding weekends) times a blended rate for labor costing. I also have a holiday date with dates that I would like to exclude from working days as well. I would be happy to share my table structure if that would help.

Thanks.
Rita


#2

Rita,

You can share your data structure if you wish. Here is a great video from Sam if you have not viewed it before that will start you along this path.

Thanks
Jarrett

Enterprise%20DNA%20Expert%20-%20Small


#3

Rita,
The video is excellent for this.

Please also consider to use Power Query. Under “Transform” , “Date & Time Column” , you will find many options for duration, bear in mind the date table in the video shows 0=Sunday. I use 7=Sunday, if you do as well, you will have to amend this, making it easier to split workingdays by <=5.
Paul

Enterprise%20DNA%20Expert%20-%20Small


#4

So the issue I have is that we run labor actuals quarterly and that quarter will end on the last friday of the last quarter month. I have the quarters start and end dates and need to be able to have the current networking days to calculate the total labor cost. Meaning if we are mid quarter, I only want the calculation to show the networking days. My calculation is Employee Cost: Blended Rate * Working Days * (8-(8*Overhead %))


#5

I have not tried this, lacking the context and data but used it before;
add a column in your date table “IsWorkingDays” with some logic to identify weekday/weekend/holiday.
(SWITCH(WEEKDAY([Date]);1;0;7;0;1)
(use 1 for working day, 0 weekend or holiday)
CALCULATE(SUM(datetable[IsWorkingDays];DATESBETWEEN(dates[date], startdate;enddate))


#6

just picked this up from Alberto Ferrari.