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.

Joe KohlheppMatt,

This works great and save me a lot of time.

Great job!

Thank You.

Tyler OlsenThis is fantastic! Thank you

Chris GreensladeThanks, 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?

Chris GreensladeHi 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

Matt AllingtonI 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)

Chris GreensladeThanks, 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?

Matt AllingtonThere 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.

JosemarFantastic! Perfect solution! Congratulations! It was exactly what I needed. Thanks.

JoeHi 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

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

ErikaHey 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

Matt AllingtonPresumably 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

JonHi Matt,

I love your post which enlightens me.

I am wondering why use Calculatetable.

Why not use Filter function here?

Please advise

Jon

Matt AllingtonYou 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

MRGreat 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!

Rodrigo SantosHi 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!

Matt AllingtonThanks Rodrigo

John ThomasJust 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.

William HooverVery interesting. Thanks for taking the time to share valuable ideas.