Calculating business hours in Power BI using DAX

DAX includes several time intelligence functions that enable you to build and compare calculations over different time periods, but calculating working hours is not one of them.

This article will explain how to calculate the business hours between start and finish Date/Time excluding weekends and holidays using DAX.

Sample Data:

I created the following data samples with the combined Date and Time columns and separate Date and Time columns.

Assumptions:

1. Business hours between the Start and Finish need to be calculated for each item.

2. Weekends should be excluded from the calculation (In this calculation, Sunday and Saturday are excluded).

3. The standard Business hours - from 8:00 AM to 5:00 PM (you can change that in the DAX code)

Calculation Methods:

In the sample file, I added different calculations for different scenarios, with two formats of outputs.

Scenarios:

• Start and Finish columns are combined Date/Time columns (2 Columns).

• Start and Finish are separated into Date and Time columns (4 Columns).

• Holidays need to be excluded from the calculation. [DAX Code with using Calendar\Date Table]

Output Format:

• Time format: Time Format can not be used to show the duration in hh: mm because the hour value is limited to 24h, so the output is originally in the Text format (hh: mm).

• Number Format: the output is in the 0.00 format

DAX Code:

I used the TRUNC function to truncate a number to an integer by removing the decimal for the Time format, so rather than two last rows in the Number format codes, I used the below codes:

Output:

Here is the sample file:

Calculate Business Hours - DAX

.zip

Download ZIP • 184KB