Mastering Business Hours Calculation in Power BI: A Deep Dive

Welcome to an exploration of a powerful method for calculating business hours between two timestamps in Power BI. This approach is particularly useful when determining the total hours spent on a task or service ticket. What sets it apart is its consideration for not just the typical working hours and lunch breaks, but also weekends and public holidays.

This simple yet effective technique was ingeniously designed by @Alessandro, a renowned Power BI expert. Let’s immerse ourselves in a practical scenario to truly appreciate Alessandro’s solution: imagine you have a customer support ticket and you want to calculate the total working hours spent resolving it. You have the timestamps for when the ticket was opened and when it was closed. By applying Alessandro’s code, you can determine exactly how many productive hours were expended on the ticket.

The code, broken down, looks like this:

Delta Hours = 
VAR _MinDate = 'Event Logs'[opened_at]
VAR _MaxDate = 'Event Logs'[resolved_at]
VAR _TimeMinDate = TIME( HOUR( _MinDate ), MINUTE( _MinDate ), 0 )
VAR _TimeMaxDate = TIME( HOUR( _MaxDate ), MINUTE( _MaxDate ), 0 )
VAR _Fail = ( _MinDate > _MaxDate ) || ISBLANK( _MinDate ) || ISBLANK( _MaxDate )
VAR __TableHolidays = DISTINCT(Holidays[Holiday Date])
RETURN 
    IF( _Fail , 0, 
            VAR _DatesToExclude = __TableHolidays
            VAR _ValidDates = NETWORKDAYS( _MinDate, _MaxDate, 1, _DatesToExclude )
            VAR _Time1 = TIME( 8, 30, 0 )
            VAR _Time2 = TIME( 12, 30, 0 )
            VAR _Time3 = TIME( 13, 30, 0 )
            VAR _Time4 = TIME( 17, 30, 0 )
            VAR _Test1 = _ValidDates > 0
            VAR _Minus = _Time2 - MIN( _Time2, _TimeMinDate ) + _Time4 - MAX( _Time3, _TimeMinDate )
            VAR _Plus = MIN( _Time2, _TimeMaxDate ) - _Time1 + MAX( _Time3, _TimeMaxDate ) - _Time3
            VAR _Calc = ((( _ValidDates - 1 * _Test1 ) * 8 * 60 )
                - ( 8 * 60 - ( HOUR( _Minus ) * 60 + MINUTE( _Minus ) ))
                + ( HOUR( _Plus ) * 60 + MINUTE( _Plus ) ))
                / 60 
            RETURN _Calc )

This intuitive code begins by declaring two variables representing the timestamps when the ticket was opened and resolved. These timestamps are then converted into a time format. The code also checks if the data has any issues, such as the ticket being marked as resolved before it was opened or missing timestamps, and returns 0 in such instances to avoid errors.

The magic unfolds when the code takes into account public holidays. This is achieved by creating a distinct list of holiday dates from a table named ‘Holidays’. This table of holiday dates can be generated using the tool Bravo, developed by @SQLBI. Bravo is a powerful tool that allows you to select a country and automatically generates a table of public holidays for that country. If you already have a defined holidays table in your database, that’s great! If not, Bravo is a reliable alternative.

The code then calculates the number of valid working days between the two timestamps, excluding the public holidays. It establishes four time variables to represent the start of the workday, the start and end of the lunch break, and the end of the workday.

The logic then confirms if there are valid working days within the given interval. Following this, it determines the working hours for the first day and the last day, accounting for the working hours and the lunch break.

Ultimately, the total working hours spent on the ticket are calculated. This computation involves accounting for full working days, adjusting for the first and last day, and converting the result into hours.

Here are some interesting aspects of this calculation:

• If the interval contains several working days, full days are considered as 8 working hours each. The first and the last day are treated differently, with their actual working hours calculated separately.
• If the ticket was opened and closed on the same day, the function calculates the precise working hours spent on the ticket, taking into account the working hours and lunch break.
• If there’s an issue with the data, or if a timestamp is missing, the function returns 0, ensuring error-free calculations.

In essence, @Alessandro‘s Power BI code offers a comprehensive and accurate way to calculate business hours between two timestamps. This effective technique can significantly benefit businesses striving to track their productivity and improve efficiency. By incorporating this method into your Power BI toolbox, you can harness its potential to provide valuable insights into your business operations, leading to data-driven decision-making.

Moreover, the integration of the Bravo tool for public holiday data offers added convenience, especially for those who do not already have a predefined holiday table in their database. With Bravo, generating a holiday table is as simple as selecting your country and letting the tool do the rest.