Latest Enterprise DNA Initiatives


Networking Days

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

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

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

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

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

Paul

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

just picked this up from Alberto Ferrari.

Paul

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

How can I update a column with values from another table? I have the IsWorkingDays in the Dates table as a True or False. I have another table Holidays that also has a IsWorkingDays column with the value of False. I want to update Dates[IsWorkingDays] matched by Date to the Holidays table to pull over the False for the Holiday Date records in Dates table.

If you are trying to update the table “Holidays” (which is on the “many” side of the relationship), then you can easily use the function RELATED().
In this case, RELATED( Dates[IsWorkingDays] ).

I decided to merge the tables so that I could get a IsHoliday column in the Dates table. Makes my measures easier to write. Thanks for the tip.