cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
juliusj
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
Anonymous
Not applicable

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

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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

View solution in original post

@Anonymous 

 

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?

 

Anonymous
Not applicable

@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

@Anonymous 

 

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. 

 

 

Anonymous
Not applicable

@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

@Anonymous 

 

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.

 


 

Anonymous
Not applicable

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

Anonymous
Not applicable

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

v-yuta-msft
Community Support
Community Support

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors