cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RadiArg31
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!

 

RadiArg31_0-1638983321281.png

 

RadiArg31_1-1638983321283.png

 

TableStart_dtEnd_dt
Table_0105/12/2021 08:5105/12/2021 09:49
Table_0105/12/2021 12:3505/12/2021 13:53
Table_0105/12/2021 20:1105/12/2021 20:39
Table_0205/12/2021 15:3305/12/2021 16:00
Table_0205/12/2021 18:3005/12/2021 18:56
Table_0205/12/2021 12:4005/12/2021 13:18
Table_0205/12/2021 23:3906/12/2021 00:11
Table_0305/12/2021 10:4005/12/2021 10:49
Table_0305/12/2021 12:1805/12/2021 14:19
Table_0305/12/2021 16:1105/12/2021 16:42
Table_0305/12/2021 17:1705/12/2021 17:50
Table_0405/12/2021 10:5005/12/2021 11:08
Table_0405/12/2021 12:4905/12/2021 13:03
Table_0405/12/2021 13:2005/12/2021 13:21
Table_0405/12/2021 13:4005/12/2021 13:41
Table_0405/12/2021 17:5105/12/2021 18:06
Table_0405/12/2021 20:0705/12/2021 20:40
Table_0505/12/2021 12:4305/12/2021 14:52
Table_0505/12/2021 16:1405/12/2021 16:16
Table_0505/12/2021 18:0505/12/2021 18:26
Table_0505/12/2021 20:3205/12/2021 21:48
Table_0605/12/2021 12:5005/12/2021 13:17
Table_0605/12/2021 20:2205/12/2021 20:36
Table_0705/12/2021 12:1305/12/2021 13:42
1 ACCEPTED SOLUTION
lbendlin
Super User
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 ?

 

lbendlin_0-1639268304972.png

 

View solution in original post

6 REPLIES 6
v-henryk-mstf
Community Support
Community Support

Hi @RadiArg31 ,

 

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

RadiArg31
New Member

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

lbendlin
Super User
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 ?

 

lbendlin_0-1639268304972.png

 

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.

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

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.