Calculate number of workable hours for each user

[LyonsBI_BRL]

I'm currently working on a task where I'm needing to calculate the number of workable hours for each month. However, if a users dateEnd date is October 15th, 2021, the calculation needs to accomodate for that. Essentially I'd have a column for each months (October, November, December to start with) on calculating the number of workable hours each month for each.

My Networking Days function looks like this below. 

// fnNETWORKDAYS
let func =   
(StartDate as date, EndDate as date, optional Holidays as list, optional StartOfWeek as number) =>
let
    // optional StartOfWeek, if empty the week will start on Monaday
    startOfWeek = if StartOfWeek = null then 1 else StartOfWeek,
    
    // Providing for logic where EndDate is after StartDate
    Start = List.Min({StartDate, EndDate}),
    End = List.Max({StartDate, EndDate}),

    // Switch sign if EndDate is before StartDate
    Sign = if EndDate < StartDate then -1 else 1,

    // Get list of dates between Start- and EndDate
    ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)),

    // if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays;
    // otherwise continue with previous table
    DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )),

    // Select only the first 5 days of the week 
    // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week
    DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, startOfWeek) < 5 ),

    // Count the number of days (items in the list)
    CountDays = List.Count(DeleteWeekends) * Sign
in
    CountDays ,
documentation = [
Documentation.Name =  " Date.Networkdays.pq ",
Documentation.Description = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in holidays. ",
Documentation.LongDescription = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in (optional) holidays. ",
Documentation.Category = " Date ",
Documentation.Source = " www.TheBIccountant.com https://wp.me/p6lgsG-2fA .   ",
Documentation.Version = " 2.1 Catering for negative duration",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description =  "  ",
Code = "  ",
Result = "  "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

I do have a Working Days Date table to reference off of as well as Holidays so that it'll know to ignore Holidays.

Any suggestions would be greatly appreciated here. 

Thanks!

[amitchandak]

@LyonsBI_BRL , As I do not see any timestamp , you can use networkdays * 8

https://skillwave.training/networkdays/

In DAX

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)) * 8

[LyonsBI_BRL]

@amitchandak 

What if I was to start with calculating the number of days for the month of October, and seeing if those days fall within the dateBegin and dateEnd columns? Accounting for holidays and weekends?

[v-rzhou-msft · Accepted solution]

Hi @LyonsBI_BRL 

I suggest you to do this by dax in Report view. Here I create a sample to show you the way.

My Sample:

I have a Holiday table with all holidays and dates for USA from 2020 to 2021.

Then I create a calendar table and build a relationship between Holiday table and calendar tables.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"YearMonth",YEAR([Date])*100+MONTH([Date]),"WEEKDAY",WEEKDAY([Date],1))

Then create a calculated column in calendar table to distinguish whether this day is holiday/weekend or not.

Workingday or Not = IF('Calendar'[Date] IN VALUES('List of Holidays in USA'[Date])||'Calendar'[WEEKDAY] in {6,7},0,1)

Then create measurs to calculate working days and hours.

Working days = 
COUNTROWS(FILTER('Calendar','Calendar'[Date]>=MAX('Table'[dateBegin])&&'Calendar'[Date]<=MAX('Table'[dateEnd])&&'Calendar'[Workingday or Not] = 1))

Working hours = SUMX('Table',[Working days])* 8

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.