cancel
Showing results for
Did you mean:
New Member

## Sum of overlapping minutes and seconds

Hi, all. I have a really interesting problem to solve, and the best way I can explain it is with seating tables in a restaurant.

Say a restaurant has 7 tables. The owner wants to see if there is a need for another table. To find this out, he wants to calculate for how long (how many minutes) all tables are occupied at the same time (meaning customers have to wait for a table). We have the start and end timestamps of each party of customers sitting down at a table, and the table number where they sit (Table_01 to Table_07). We need to calculate for how long (in minutes), ALL 7 tables are occupied at the same time. This may happen once or multiple times a day.

I have a data sample for 5th December, visualised below in a Gantt chart format (Craydec Timelines visual). We can see that there is a short time when all 7 tables’ occupancy overlaps in time. This happens from 05/12/2021 12:50:17, when customers sit down at Table_06, until 05/12/2021 13:03:08, when another party of customers leaves from Table_04 (both are in bold below in data sample). This means that, in that day, only for the difference of the latter time minus the former, which is 12 minutes and 51 seconds, the restaurant is full with all 7 tables occupied (between red lines in picture #2).

For my case, I would need to actually get a DAX measure to calculate this duration (in the above example 12 minutes and 51 seconds), and then, seeing as it will occur more than once, get the sum of all these overlapping instances durations. The full date range will cover months, not only a day, so needs to be filterable by a date range slicer.

Your help would be much appreciated, as I am finding it hard to break down the problem and start solving it in DAX. Many thanks!

 Table Start_dt End_dt Table_01 05/12/2021 08:51 05/12/2021 09:49 Table_01 05/12/2021 12:35 05/12/2021 13:53 Table_01 05/12/2021 20:11 05/12/2021 20:39 Table_02 05/12/2021 15:33 05/12/2021 16:00 Table_02 05/12/2021 18:30 05/12/2021 18:56 Table_02 05/12/2021 12:40 05/12/2021 13:18 Table_02 05/12/2021 23:39 06/12/2021 00:11 Table_03 05/12/2021 10:40 05/12/2021 10:49 Table_03 05/12/2021 12:18 05/12/2021 14:19 Table_03 05/12/2021 16:11 05/12/2021 16:42 Table_03 05/12/2021 17:17 05/12/2021 17:50 Table_04 05/12/2021 10:50 05/12/2021 11:08 Table_04 05/12/2021 12:49 05/12/2021 13:03 Table_04 05/12/2021 13:20 05/12/2021 13:21 Table_04 05/12/2021 13:40 05/12/2021 13:41 Table_04 05/12/2021 17:51 05/12/2021 18:06 Table_04 05/12/2021 20:07 05/12/2021 20:40 Table_05 05/12/2021 12:43 05/12/2021 14:52 Table_05 05/12/2021 16:14 05/12/2021 16:16 Table_05 05/12/2021 18:05 05/12/2021 18:26 Table_05 05/12/2021 20:32 05/12/2021 21:48 Table_06 05/12/2021 12:50 05/12/2021 13:17 Table_06 05/12/2021 20:22 05/12/2021 20:36 Table_07 05/12/2021 12:13 05/12/2021 13:42
1 ACCEPTED SOLUTION
Super User

@RadiArg31 This is relatively easy to do in DAX .  But you mentioned that you need it down to second level granularity. That would be very expensive to calculate.  Will it be sufficient to do it on minute level ?

6 REPLIES 6
Community Support

Whether the advice given by @lbendlin  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.🙂

Best Regards,
Henry

New Member

Hi @lbendlin thanks for your answer. Yes minutes would be sufficient.

Super User

@RadiArg31 This is relatively easy to do in DAX .  But you mentioned that you need it down to second level granularity. That would be very expensive to calculate.  Will it be sufficient to do it on minute level ?

New Member

Hi @lbendlin your solution is great thank you, I was wondering if you could let me know how I could have a GENERATESERIES() statement that doesn't stop at 1439 (GENERATESERIES(0,1439)) as I want to apply this to multiple days not just one, let's say the latest month or 2, and so a hard stop is not very useful if that makes sense? It has to be flexible and increase as the number of rows increases.... Thanks again.

Super User

The solution I provided already does that. If you noticed, one of the visits lasted past midnight.

If you know of a way to have more than 1440 minutes in a day I am all ears ..

New Member

Oh they are minutes per day, ok I understand the logic now, thanks.

Announcements