cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
juliusj Frequent Visitor
Frequent Visitor

Calculating Network Hours / Working Hours

Hello,

 

I have been trying to calculate Network hours(i.e. working time exculding non business hours, weekends, and holidays). I have tried some other solutions(listed below). However, my dataset seems to be more complex. 

 

Withint the data we have the following coloums:

Date/Time OpenedDate/Time ClosedCase NumberExpected Output
2/11/19 3:24 PM2/11/19 3:24 PM10
2/12/19 2:21 PM 2null
2/27/19 3:17 PM2/27/19 3:17 PM30
3/14/19 10:10 AM 4null
2/16/19 7:04 PM2/18/19 9:42 AM51.7
2/15/19 4:32 PM2/16/19 11:06 AM60.966667

 

Context:
I'm trying to caluclate time to solve a customer support case. I have tried following the following solutions.

 

However, since cases can be open after business hours, be closed after business hours, or not be closed yet I have been running into a lot of problems. 

 

The reqiuirements:

  • Business Day: 8am - 5pm
  • Weekends: Saturday and Sunday
  • I would also like to include a holiday calendar.
  • Ideally would also work with a direct query[optional]

Any help or direction would be much appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
natelpeterson New Contributor
New Contributor

Re: Calculating Network Hours / Working Hours

@juliusj -

 

Here's the date table that is referenced in the Calculated Column. Notice, it has a Weekend attribute to indicate whether it's a weekend. You could add OffDay, which considers both weekends and holidays.

Date = ADDCOLUMNS(CALENDAR(DATE(2018,1,1),DATE(2020,12,31)),"Weekend",IF(WEEKDAY([Date]) IN {1,7}, TRUE(),FALSE()))

 

Here's a Calculated Column. It works with weekends. The same logic could be used with Is OffDay instead of Weekend. You'd replace the following bits: 

var start_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Opened]))

var end_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Closed]))

'Date'[Weekend] = FALSE()

 

Hours Difference = 
//Define hours of workday, in seconds
var work_day_begins = 3600 * 9 //9AM
var work_day_ends = 3600 * 17  //5PM
var seconds_in_workday = work_day_ends - work_day_begins

//Check whether start/end dates occurred on a weekend.
var start_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Opened]))
var end_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Closed]))

//Get the seconds from midnight. 
//If it's a weekend, always use the end of the workday value.
//If outside working hours, snap to the start or end of day.
var start_time = TIMEVALUE(FORMAT([Date/Time Opened],"HH:mm:ss"))*86400
var end_time = TIMEVALUE(FORMAT([Date/Time Closed],"HH:mm:ss"))*86400
var start_time_adj = IF(start_date_is_weekend,work_day_ends,MIN(MAX(start_time,work_day_begins),work_day_ends))
var end_time_adj = SWITCH(
    TRUE(),
    ISBLANK(end_date_is_weekend),BLANK(),
    end_date_is_weekend,work_day_ends,
    MIN(MAX(end_time,work_day_begins),work_day_ends)
)

//Find the number of workdays
var day_diff = COUNTROWS(
    FILTER(
        'Date',
        [Date] > INT([Date/Time Opened]) 
        && [Date] <= INT([Date/Time Closed]) 
        && 'Date'[Weekend] = FALSE()
    )
)

//Final calculation:
var time_diff = end_time_adj - start_time_adj
var working_seconds = (day_diff * seconds_in_workday) + time_diff
var working_hours = working_seconds / 3600.00
return IF(ISBLANK([Date/Time Closed]),BLANK(),working_hours)
9 REPLIES 9
Community Support Team
Community Support Team

Re: Calculating Network Hours / Working Hours

@juliusj ,

 

I would like to suggest you to refer to video and blogs below to create your measures:

https://www.youtube.com/watch?v=GLIoDbOiJgw

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362

 

Community Support Team _ Jimmy Tao

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

natelpeterson New Contributor
New Contributor

Re: Calculating Network Hours / Working Hours

@juliusj -

 

Here's the date table that is referenced in the Calculated Column. Notice, it has a Weekend attribute to indicate whether it's a weekend. You could add OffDay, which considers both weekends and holidays.

Date = ADDCOLUMNS(CALENDAR(DATE(2018,1,1),DATE(2020,12,31)),"Weekend",IF(WEEKDAY([Date]) IN {1,7}, TRUE(),FALSE()))

 

Here's a Calculated Column. It works with weekends. The same logic could be used with Is OffDay instead of Weekend. You'd replace the following bits: 

var start_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Opened]))

var end_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Closed]))

'Date'[Weekend] = FALSE()

 

Hours Difference = 
//Define hours of workday, in seconds
var work_day_begins = 3600 * 9 //9AM
var work_day_ends = 3600 * 17  //5PM
var seconds_in_workday = work_day_ends - work_day_begins

//Check whether start/end dates occurred on a weekend.
var start_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Opened]))
var end_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Closed]))

//Get the seconds from midnight. 
//If it's a weekend, always use the end of the workday value.
//If outside working hours, snap to the start or end of day.
var start_time = TIMEVALUE(FORMAT([Date/Time Opened],"HH:mm:ss"))*86400
var end_time = TIMEVALUE(FORMAT([Date/Time Closed],"HH:mm:ss"))*86400
var start_time_adj = IF(start_date_is_weekend,work_day_ends,MIN(MAX(start_time,work_day_begins),work_day_ends))
var end_time_adj = SWITCH(
    TRUE(),
    ISBLANK(end_date_is_weekend),BLANK(),
    end_date_is_weekend,work_day_ends,
    MIN(MAX(end_time,work_day_begins),work_day_ends)
)

//Find the number of workdays
var day_diff = COUNTROWS(
    FILTER(
        'Date',
        [Date] > INT([Date/Time Opened]) 
        && [Date] <= INT([Date/Time Closed]) 
        && 'Date'[Weekend] = FALSE()
    )
)

//Final calculation:
var time_diff = end_time_adj - start_time_adj
var working_seconds = (day_diff * seconds_in_workday) + time_diff
var working_hours = working_seconds / 3600.00
return IF(ISBLANK([Date/Time Closed]),BLANK(),working_hours)
juliusj Frequent Visitor
Frequent Visitor

Re: Calculating Network Hours / Working Hours

@natelpeterson 

 

Thank you so much for your help. I did get this query to work with a static data set.

 

However, in direct query mode the following code does not work:

//Find the number of workdays
var day_diff = COUNTROWS(
    FILTER(
        'Date',
        [Date] > INT([Date/Time Opened]) 
        && [Date] <= INT([Date/Time Closed]) 
        && 'Date'[Weekend] = FALSE()
    )
)

Error Message: Function 'COUNTROWS' is not allowed as part of calculated column DAX expressions on DirectQuery models.

 

Is there any work around to this or am I just out of luck?

 

natelpeterson New Contributor
New Contributor

Re: Calculating Network Hours / Working Hours

@juliusj - We can either change it to a measure, or re-work the logic. Changing to measure makes sense for DirectQuery.

Hours Difference Measure = 
//Define hours of workday, in seconds
var work_day_begins = 3600 * 9 //9AM
var work_day_ends = 3600 * 17  //5PM
var seconds_in_workday = work_day_ends - work_day_begins

//Put Opened, Closed into variables.
var opened = MAX([Date/Time Opened])
var closed = MAX([Date/Time Closed])

//Check whether start/end dates occurred on a weekend.
var start_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT(opened))
var end_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT(closed))

//Get the seconds from midnight. 
//If it's a weekend, always use the end of the workday value.
//If outside working hours, snap to the start or end of day.
var start_time = TIMEVALUE(FORMAT(opened,"HH:mm:ss"))*86400
var end_time = TIMEVALUE(FORMAT(closed,"HH:mm:ss"))*86400
var start_time_adj = IF(start_date_is_weekend,work_day_ends,MIN(MAX(start_time,work_day_begins),work_day_ends))
var end_time_adj = SWITCH(
    TRUE(),
    ISBLANK(end_date_is_weekend),BLANK(),
    end_date_is_weekend,work_day_ends,
    MIN(MAX(end_time,work_day_begins),work_day_ends)
)

//Find the number of workdays
var day_diff = COUNTROWS(
    FILTER(
        'Date',
        [Date] > INT(opened) 
        && [Date] <= INT(closed) 
        && 'Date'[Weekend] = FALSE()
    )
)

//Final calculation:
var time_diff = end_time_adj - start_time_adj
var working_seconds = (day_diff * seconds_in_workday) + time_diff
var working_hours = working_seconds / 3600.00
return IF(ISBLANK(closed),BLANK(),working_hours)

However, this only works for single rows - the total doesn't work. We can get an Additive Measure by wrapping a SUMX:

 

Hours Difference Measure With Total = SUMX('DateTime Test', [Hours Difference Measure])

Unfortunately, these may not perform well for large data sets. I'd love to hear about what type of performance you experience!

 

Regards,

Nathan

juliusj Frequent Visitor
Frequent Visitor

Re: Calculating Network Hours / Working Hours

@natelpeterson 

 

The solution is quite slow ~6-7 minute load times. I'm working with a data set that is 27K rows that grows each day. Having totals is a pretty big deal for my use case. We are hoping to measure individual and team performance. 

 

 

natelpeterson New Contributor
New Contributor

Re: Calculating Network Hours / Working Hours

@juliusj - Some performance considerations:

 

Have 2 date tables, each with a relationship with your table. This would require adding columns with Date datatype insead of Date/Time. The Date table could also have a column "relative work days" which could be used to derive the difference between them, instead of counting rows.

 

You could also have a Time table for each of Start and End. It could have a column which maps all times before working hours to the beginning of the work day and all times after working hours to the end of the work day, and also contain the second of the day. Again, each table would be related to your fact table. Performance could be improved by rounding to the nearest minute and only having 1440 rows in your Time table instead of 86400.

 

All 4 of these tables should be built in your data source. The good news is they can be built out and can be static.

 

This link describes performance considerations.

 

Cheers!

Nathan

natelpeterson New Contributor
New Contributor

Re: Calculating Network Hours / Working Hours

In fact, ALL of the processing could potentially be done in the source, since it is a row-by-row calculation.

juliusj Frequent Visitor
Frequent Visitor

Re: Calculating Network Hours / Working Hours

@natelpeterson 

 

I just want to make sure I'm understanding you correctly. Performing the above would create the tables needed to mimic the below logic:

 

//Find the number of workdays
var day_diff = COUNTROWS(
    FILTER(
        'Date',
        [Date] > INT([Date/Time Opened]) 
        && [Date] <= INT([Date/Time Closed]) 
        && 'Date'[Weekend] = FALSE()
    )
)

@juliusj - Some performance considerations:

 

Have 2 date tables, each with a relationship with your table. This would require adding columns with Date datatype insead of Date/Time. The Date table could also have a column "relative work days" which could be used to derive the difference between them, instead of counting rows.

 

You could also have a Time table for each of Start and End. It could have a column which maps all times before working hours to the beginning of the work day and all times after working hours to the end of the work day, and also contain the second of the day. Again, each table would be related to your fact table. Performance could be improved by rounding to the nearest minute and only having 1440 rows in your Time table instead of 86400.

 


 

natelpeterson New Contributor
New Contributor

Re: Calculating Network Hours / Working Hours

@juliusj - That's sort of correct - Instead of counting rows, you would use RELATED to retrieve each "relative work day" number, and then subtract one from the other.