Calculating Elapsed Business Hours Using DAX Part 2 - Excelerator BI

# Calculating Elapsed Business Hours Using DAX Part 2

I have learnt a lot over my 50 something years in life, and one of my greatest learnings is that “it doesn’t matter how much you already know, there is still more to learn”. Hopefully this is one of the reasons you read my blog – because you want to learn from me – we all learn from each other.

My regular readers would know that there are many reasons I like to share what I have learnt on my blog.

• I get to share what I know with others
• I get to clarify my thinking about the topic (I get to a deeper level of understanding when I have to explain it to someone else).  Stephen Covey describes this as “Third Person Learning”
• I get a permanent record of how to do it for myself, so I can find it again in the future when I need it.
• And hopefully, somewhere along the line, someone will hire me to come and train their staff or fix some problem they are having.

Then, sometimes (like this time) I discover that someone has a better way to solve the same problem that I shared on my blog. This is what happened last week after I shared my first article about how to calculate the total business hours between 2 date/time stamps. I shared the way I solved this problem last week, but one of my readers, Daniil Bogomazov, shared a brilliant alternative solution to the same problem. The solution is so good that I am sharing his solution with you here today.

## But What About Last Week’s Solution?

The solution I provided last week is a valid solution (there is more than 1 way to skin a cat). What’s more, last week’s blog was also about the process of solving a problem rather than the specific resulting formula itself. I strongly believe if I can help you understand the process, you will be a better DAX author. Note: I did get a couple of comments from last week that I made a logic error in my formula – I must fix that :-).

## DAX is a Beautiful Language

The solution I am going to share below is literally beautiful. DAX is (or should I say, can be) a beautiful, elegant language. Once you understand the underlying principles of how the language works, you can literally write elegant formulas that leverage the strengths of the data model to return the number you need. Let me show you the formula provided by Daniil, and then spend some time explaining how it works.

Beautiful and compact, don’t you think?

## Why is it so Different?

You might be wondering why this solution is so different to my solution last week. I have been thinking about that, and I believe it is because Daniil approached the problem in a different way to me. You may recall in the video last week that I used the following illustration to think about the problem.

I broke the problem into pieces and built a solution for each piece of the puzzle (first day/part day + last day/part day + full days in between). It occurs to me that Daniil thought about the problem differently. He was thinking “I need to step through each working day, one at a time, and work out how many hours on each day”. It is a different way of thinking, and importantly, it plays directly into the strengths of the DAX language. DAX is very strong in 2 areas – filtering tables and iterating through rows in tables. So the thought process from Daniil plays directly to the strengths of DAX – there is a lesson in that for all of us.  If you can think like the engine, you will write better DAX.

## Differences in Daniil’s Model vs Mine

Before starting to explain this solution, I should point out that Daniil did not split the Tickets open and end columns into separate date and time columns. Instead he kept these as combined date/time columns.

This is needed for this solution to work because the date AND time forms part of the solution. Generally speaking it is not good practice to load data in date/time columns and it is generally advised to split these into the component parts. It would definitely be possible to still split the columns (best practice) and then rebuild the date/times (as needed by this solution) inside the DAX measure. In production, if size and performance were issues, I would definitely consider doing that.  I have shown you how at the end of this page.

The second thing to note is that there are no relationships in this model.

That makes sense when you think about it, because the calendar table primary key column is type “date” and the HelpDeskTickets table is Date/Time, therefore they can’t be joined (not sensibly, anyway). The last difference is that Daniil has hard coded the start and end date/time for business hours into each row in the calendar table. This has an added benefit of effectively supporting the possibility of different business hours on different days, if needed. In my alternate formula at the bottom of the page, it is not necessary to store these as date/time columns (best practice again).

## Let me Decompose the Formula For You Below

As is often the case in the DAX language, you need to start in the middle of a formula to work out what it does. Indeed, it is often the case that DAX formulas are written from the inside out. This is especially true for this type of solution that uses tables, iterating functions, and calculated columns.

### Let me start with lines 6 – 10 (shown again below)

This section creates a copy of the calendar table, keeping only the working days (lines 6,7 & 9 do this). Line 8 passes an additional filter to the Calendar table from each ticket in the HelpTickets table, keeping just the rows in the Calendar table that span the days the ticket was open. Lines 6 through 9 complete this task, one ticket at a time (back to that shortly). Now I said above”copy” of the calendar table, but you can “think” of this formula working in one of 2 different ways. Either think of a virtual copy of the table including lineage back to the original calendar table, or you can think of the original calendar table in the model being filtered directly. Both thinking approaches give the same effect – the calendar table only contains the days you care about at the time you are using it, which happens to be list of working days spanning the period in which the ticket is open. Because this formula is a calculated column, it is the iteration of the calculated column that passes each HelpTicket to this part of the formula, one row at a time. You can “imagine” therefore that each row in the calculated column has its own “subset” of the calendar table specifically filtered for use in the next part of the formula.

### Line 5 is a SUMX over this table

So line 5 (and the matching closing bracket in line 12) is the iterating function. The SUMX steps through the filtered calendar table created above, and then completes the calculation shown in line 11 for every row in the filtered calendar table. It is this SUMX iterator that cycles through the subset filtered calendar table, one row at a time, to calculate the total business hours for each ticket.

### So let’s look at line 11 in detail

This is quite a tricky line of code, but if you break it down, the logic is quite simple.

Starting with part 2 (above), it says “which date/time is bigger; is it the start time in the calendar table (for this row I am looking at) or is it the start time in the tickets table”? If I refer back to my illustration of the working days in a week, it is saying “which comes first, 1 or 2 (in the image below). In other words, was the ticket open before or after the start of the business hours for this day? It keeps the latest one by using the MAX function.

Going back to the original formula (shown below again for convenience)…

Looking at part 1 of the formula above, the question is “which comes first; the close of the ticket (3 in the time line above) or the end of the business day (4 shown in the time line)”?.

Now line 11 is pretty clever, because if it is the first day or the last day of the ticket, it correctly handles the “part day” if necessary. For all other days, it correctly calculates the full business hours for that day.

## Finally, My Modified Version

As I mentioned above, it may be beneficial to split apart the date/time columns in larger models (it would not make any material impact on a model this small, but may benefit larger models). I have included a modified version that does this for completeness, and you can see that you can simply rebuild the date/time stamps inside the formula as needed.

This is a general technique that you can use on any date/time column. You always need to take into account the extra effort for the engine to re-join the columns and also the slightly more complex DAX vs the benefit of saving the storage space.

I have attached the workbook here if you want to take a look.

### 37 thoughts on “Calculating Elapsed Business Hours Using DAX Part 2”

1. Hy Matt, thank you for great solution, really helpful.
I am struggling to calculate something same but in Business Days. I have 3 Days SLA that a ticket should be closed within 3 days of creation. how can I calculate that? to see if the ticket was closed withing 3 business days of its creation inclusive of all aspects mentioned above.

Thank you

1. I’m not really sure what you’re asking. Maybe just divide the answer by 8, assuming an 8 hour day

2. Hi Matt,

1. My workbook is linked at the bottom of the article

3. Thanks for exceptionally clear article. This solved my problem completely. I appreciate you taking the time to write it.

4. I frequently search for and read articles like this. Well, articles that wish they were like this. Thank you so much for explaining how the functions work. Realizing the CALCULATETABLE creates a table variable (to use a MSSQL analogy) was the bridge that helped that concept cross over into knowledge for me. I truly appreciate you for the time and effort you put into writing this article.

5. Roman Tesolkin Tesolkin

Hi Matt!

Could you please help what should be updated in DAX to add Calculation of Business Hours for different countries with different Public Holidays, so for tickets issued in Spain for example were excluded spanish Public holidays, same for Portugal etc:

Start 09:00
End 18:00

Holiday 2021 Country
New Year’s Day Friday, 1 January 2021 Spain
Good Friday Friday, 2 April 2021 Spain
Labor Day Saturday, 1 May 2021 Spain

Freedom Day Sunday, 25 April 2021 Portugal
Republic Day Tuesday, 5 October 2021 Portugal

Thanks

1. Brilliant Matt, works a treat! Couple of points arose as I developed my pattern from the above:
if you already have a relationship between fact and calendar dim table then add filter expression
CROSSFILTER( ‘CalendarSplitDate'[Date], HelpDeskTicketsSplit[SomeDateColumn], None)
the final line requires a little explanation:
If SUMX returns empty table, then condition is false and so return zero
often, if the end datetime is blank, then you want to return zero so wrap the _WorkHours variable in an IF statement e.g.
IF( ISBLANK(TicketEnd), 0, ….) // and ditto for Start datetime if required

6. Hello

I have tried the above formula with time and date split and time and dates joined, every way from Sunday. The results do not compute at all i am getting zero times. i cant imagine why this isnt working

1. Even on with the columns that are open and closed on the same day i am getting zero.
Frustrating

2. DAX can be tricky. Clearly something is wrong, as you can see the formula I have written works, so there is something different about what you have done. It is impossible for me to say what that could be without seeing it. I don’t/can’t provide free support here, but you can ask for help at community.powerbi.com. Alternatively I offer commercial support at http://xbi.com.au/matt

1. Matt,
how does the formula you provide deal with tickets that are opened and closed on the same day. For those tickets i keep getting a 0 for those types of tickets. Also thanks for taking the time to respond

Jeff D

1. well, my solution takes the start time and end time. If it is the same day, it will work. But it depends on your data. If you open and close a ticket on the same day and you don’t record time, then you need to work out how you want to handle it. I guess you could use 0, 0.5, or 1.0, and all of these would have some merit.

7. Matt,

This works great and save me a lot of time.

Great job!

Thank You.

Thanks, Matt.

But I would like to be able to see this in and out dynamically by date and time. I’m not sure what the DAX would look like for that.

How would I write a cumulative measure for the number of vehicles entering based on date and time of entry?

Hi Matt,

Great blog. I have a similar situation, but with Car Park Occupancy. I have a Purchase Start Date & Time and also a Purchase End Date & Time and need to count the number of cars going in and out, rather than measuring a time duration. I also have a similar case, in that a car may be parked in a car park overnight and therefore spanning two days.

Would you current DAX take account of this scenario? If so, what amendments would need to be made?

Thanks

1. I don’t think this approach is right for your use case. I think you need a “perpetual inventory” approach.
1. Write a measure that calculates the cumulative number of vehicles entering without regards for those leaving
2. Write a measure that calculates the cumulative number of vehicles leaving without regards for those entering

subtract 2 from 1 to get the current total in the carpark (sorting by date)

Thanks, Matt.

But I would like to be able to see this in and out dynamically by date and time. I’m not sure what the DAX would look like for that.

How would I write a cumulative measure for the number of vehicles entering based on date and time of entry?

1. There are quite a few potential issues I can think of. How many rows in your data table? If you contact me via the contact form below and send me some sample data (anonymised), I may write a blog on how to solve it – it sounds like an interesting problem.

10. Hi Matt,

Great Post! Currently the Dax script is rounding to the nearest hour. How can I include minutes in the above script?
EX: Length of time to complete: 2.67 hours

1. I don’t see any reason why this formula would round to the nearest hour. What number format do you have on your measure?

11. Hey Matt,
Great post, great detailed write up. Thank you. Is it possible to introduce “Country” filter for different “Work hours” pattern?

Any advice would be really great.

Thank you very much,
erika

1. Presumably different work hours, but also maybe different working days. You could add another set of columns into the calendar table for each country, but you could also create a data table that joins to the calendar[date] 1:* to a new country date table. Add one set of dates for each country (hence why it needs to be 1:*) add a country column for filtering purposes, and turn on bidirectional cross filtering. That would work

1. sorry, are you able to elaborate? the calendar date table do you just mean a standard calendar table without the working hours and holidays fields i.e., date only? and the “new country date table” have all countries with working hours and holidays per day? thanks

1. It’s a big topic. If you have multiple countries with different work hours, then you need these data stored in a table somewhere so they can be referenced. you can add new columns for each country in the current calendar table, or you can add a new fact table containing rows for each country. There are probably other options, too.

12. Hi Matt,

I love your post which enlightens me.
I am wondering why use Calculatetable.
Why not use Filter function here?
Jon

1. You could use Filter instead, but Filter only takes one true/false statement. The statement would be a bit harder to write, but of course you could do it. There are many ways to write some formulas. Do what you feel comfortable with as long as it gives you the correct answer

13. Great post, helped me hugely.

Here is a tricky variation, how do we project a future date within business hours with a known variable?

Example: I have 24 business hours to reply to a ticket before breaching an SLA, I want to predict the date/time when it will be missed.

Ticket arrive: 30/10/2020 09:00
Bus Hours: M-F 07:00-18:00
SLA breach (what I want to predict): 3/11/2020 11:00

Again, thanks – excellent post!

14. Hi Matt, how are you?
I hope so, friend.
I’m from Brazil and I’m here to thank you for sharing this solution.
Its teaching methodology is simply fantastic.
Detail by detail, explaining each point of the calculation.
You make a complex problem simple to solve.
Thank you!
Be alright!

15. Just when you think you have it figured out… That happens to me a lot! Great to have a sharing community at your back! Thanks for sharing Matt and Daniil.

Scroll to Top