Counting open slots from reservations based on a few conditions

[zudar]

Hi all,

I've encountered an analytical problem I've trouble with executing in PowerBI and could probably use your help.

I've got the following single table with reservations (simplified example for a single Room 'A'):

Reservation	Room	Start	End
1	A	01-12-2019 3:00	01-12-2019 4:00
2	A	01-12-2019 6:00	01-12-2019 10:00
3	A	01-12-2019 11:00	01-12-2019 16:00
4	A	01-12-2019 17:00	01-12-2019 20:00
5	A	01-12-2019 22:00	02-12-2019 1:00
...	...	...	...

What I'd like to achieve is a matrix with individual dates on the columns and rooms on the rows, showing how many times a room was available after 5:00 for a period of at least 1.5 hours and while being reserved at least once on the same day after 5:00.

In the example above I have 5 reservations for Room A on a single day. Let me break it down:

Reservation 1 ends before 5:00 so isn't relevant for 1-12-2019 in the final matrix.

Reservation 2 ends after 5:00 so it initiates the count (remember, I needed to have at least one reservation ending after 5:00 to start the count)

Reservation 3 begins only one hour after reservation 2 ends. So no open slot of at least 1.5 hours here.

Reservation 4 begins only one hour after reservation 3 ends. So no open slot of at least 1.5 hours here.

Reservation 5 begins two hours after reservation 4 ends. So finally, an open slot of at least 1.5 hours!

Reservation 5 ends on 2-12-2019 1:00. With no known next reservation (or a reservation starting at least 1.5 hours later), I would also like to count this open slot into the matrix for date 1-12-2019!

This means that the resulting number for Room A on 1-12-2019 should be '2'!

The first one being between reservation 4 & 5. The second one being after 5.

With the example above, I would ideally get as an end result:

	01-12-2019	02-12-2019	03-12-2019	etc..
Room A	2	?	?	...
Other Room 'B'	?	?	?	...
etc..	...	...	...	...

Any idea on how to create something like this?

Your help is very much appreciated.

[V-lianl-msft · Accepted solution]

Hi @zudar ,

First you need to sort the "room" column, then the “start” column, and then create an index column.

You will use this index column to get the last end date of the same room.

You also need “start counting” column to compare  with "end" column. 

Please refer to this PBIX.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajulshah]

Hello @zudar,

Create a duplicate column of [Start] and change the data type to 'Date' and use this column for relationship to Date dimension table.
Then, create a calculated column as below:

Time Difference = 
VAR StartTime = Reservations[End]
VAR EndTime = CALCULATE(MIN(Reservations[Start]),FILTER(Reservations,Reservations[Reservation]=EARLIER(Reservations[Reservation])+1))
VAR Difference = DATEDIFF(StartTime,EndTime,HOUR)
RETURN IF(Difference>1,1)

Then, create a calculated measure as below:

Total Time Slots = COUNTX(FILTER(Reservations,Reservations[Time Difference]=1),Reservations[Reservation])

And you can plot this measure in the matrix.

Hope this helps. 

[zudar]

Hi @rajulshah ,

Thanks for looking into this! 🙂 I must say I was quite suprised to see such a short and efficient solution; I also don't see how you are accounting for the 5:00 mark.. or the 1.5 hours minimum.

Let me show you what happened when I changed the data, added a new room in the reservations table and applied your solution:

 Let me break it down:

Room A, on date 1-12-2019:

Reservation 1 from 3:00 to 6:00, so it has a reservation ending after 5:00 which initiates the count.

Reservation 3 starts at 9:00, so we have 1 open slot of at least 1.5 hours. Reservation 3 ends at 10:00.
Reservation 5 starts at 11:00, so we don't have an open slot of at least 1.5 hours here. Reservation 5 ends at 16:00.

Reservation 8 starts at 19:00, so we have a second open slot of at least 1.5 hours. Reservation 8 ends at 20:00.

Reservation 9 starts at 22:00, so we have a third open slot of at least 1.5 hours. Reservation 9 ends at 1:00 (the 'next' day).

Reservation 11 starts at 4:00 (the 'next' day), so we have a fourth open slot of at least 1.5 hours that must be included for 1-12-2019.

--> So the result for Room A, on date 1-12-2019 = 4

Room A, on date 2-12-2019:
Reservation 11 from 4:00 to 6:00, so it has a reservation ending after 5:00 which initiates the count.
Reservation 14 starts at 8:00, so we have 1 open slot of at least 1.5 hours. Reservation 14 ends at 18:00.

With no next known reservation, I'd like to count another open slot for the period after 18:00 until 5:00 (the 'next' day).
--> So the result for Room A, on date 2-12-2019 = 2

Room B, on date 1-12-2019:

Reservation 2 from 4:00 to 5:20, so it has a reservation ending after 5:00 which initiates the count.

Reservation 4 starts at 9:30, so we have 1 open slot of at least 1.5 hours. Reservation 4 ends at 11:00.
Reservation 6 starts at 11:15, so we don't have an open slot of at least 1.5 hours here. Reservation 6 ends at 13:00.

Reservation 7 starts at 18:00, so we have a second open slot of at least 1.5 hours. Reservation 7 ends at 19:00.

Reservation 10 starts at 20:00, so we don't have an open slot of at least 1.5 hours here. Reservation 10 ends at 3:00 (the 'next' day).

Reservation 12 starts at 4:00 (the 'next' day), so we don't have an open slot of at least 1.5 hours here. Reservation 12 ends at 4:30 (the 'next' day).

With only half an hour left until 5:00, there is no open slot of at least 1.5 hours. 

--> So the result for Room B, on date 1-12-2019 = 2

Room B, on date 2-12-2019:
Reservation 13 from 7:00 to 8:00, so it has a reservation ending after 5:00 which initiates the count.
Reservation 15 starts at 10:00, so we have 1 open slot of at least 1.5 hours. Reservation 15 ends at 18:00.

With no next known reservation, I'd like to count another open slot for the period after 18:00 until 5:00 (the 'next' day).
--> So the result for Room A, on date 2-12-2019 = 2

I have a feeling that your solution is in the right direction, but it's not really 'it'. Could you be so kind to help me further? I'm trying to include the PBIX file here but I don't think I have that possibility..

Thanks for your help so far!

[rajulshah]

Hello @zudar,

Did you get the solution as required?

[zudar]

Hi @rajulshah , I'm going to check the solution proposed by @V-lianl-msft and then get back. Thank you for your help.