Calculate Working Hours In Power BI

Hello guys, in today’s blog I’ll show how you can calculate working hours between two dates in Power BI Data Integrations. Though Power BI has a lot of inbuilt time-intelligence functions, calculating working hours is not one of them.   (Though this can change in the future) After some research, I found that there’s no concrete solution available for this in the Power BI community, hence this blog.

Feel free to suggest any changes in the calculations so that we optimize the code further.

Before we start, make sure that you have the latest version (version after July 2020) of Power BI installed as some functions used in this solution may not be available in the older versions.

Problem:

We want to calculate the hours worked between 2 timestamps. If the dates expand across multiple days, we must make sure that we ignore the weekends from this calculation too.

Please note that we have not added a customised list of holidays in this solution, although it’s possible to do so.

In this blog we’re considering the working hours to be from 8am to 5pm, but you can modify it according to your preference.
Some issues we can have to deal with are blank dates, inconsistent start & end dates, start dates greater than end dates & so on.

Solution:

Here’s how you can create a calculated column to get the working hours between 2 dates:

• Create a column. Create variables to hold start & end dates. Make sure that your date columns follow a 24-hour format.
• Next step is to create a virtual table for dates. The date table in this solution is dynamically updated, although you can specify a date for it to start and end. All the filters regarding working hours & weekends as well as holidays can be applied here.
  Note – Make sure that you enter the ending hour as one less than your actual working hour. E.g. Enter 8 &16 if your working hours is 8:00-17:00.
• Finally, we have our main calculation. I found that the DateDiff function does not always give us the desired output when we specify conditions such as working hours. Hence, for the hours calculation I have used a different method.

Output:

Tip: To deal with inconsistent dates, instead of getting hours for all the rows, we can specify a filter at the end. Something like this:

IF(start_date=BLANK() || start_date>end_date || end_date=BLANK(),BLANK(),hours)

Here’s the full code:

Hours =

//Enter the columns for Start & End dates

var start_date = ‘New Method'[New Start Date]

var end_date = ‘New Method'[New End Date]

//Date Table

var Hours_List = SELECTCOLUMNS(GENERATESERIES((8), (16)), “Hour”, [Value])

var Minutes_List = SELECTCOLUMNS(GENERATESERIES((0), (59)), “Minute”, [Value])

var two_years_ago_start = DATE(YEAR(NOW())-2,1,1) //DATE(2018,1,1)

var one_year_later_end = DATE(YEAR(NOW())+1,12,31)

var Dates_List = CALENDAR(two_years_ago_start,one_year_later_end)

var HoursandMinutes = ADDCOLUMNS(

CROSSJOIN(Hours_List, Minutes_List),

“Time”, TIME([Hour], [Minute], 00),

“Validity”, IF([Hour]<8 || [Hour]>16,”Non working”,”Working”)

)

var DateTimeTable = ADDCOLUMNS(CROSSJOIN(HoursandMinutes,Dates_List),

“weekday”, WEEKDAY([date],2),

“New combined date”,CONVERT(CONCATENATE([date],CONCATENATE(” “,[Time])),DATETIME)

)

//Hours Calculation

var minutesfromstart = COUNTROWS(FILTER(DateTimeTable,start_date>=[New combined date] && [Validity]=”Working” && [weekday]<6))

var minutesfromend = COUNTROWS(FILTER(DateTimeTable,end_date>=[New combined date] && [Validity]=”Working” && [Weekday]<6))

var minutes = minutesfromend-minutesfromstart

var hours = DIVIDE(minutes,60)

return
IF(start_date=BLANK() || start_date>end_date || end_date=BLANK(),BLANK(),hours)

Karan Nair
Team Lead – Data Analytics
Addend Analytics