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.