Forum Discussion
Calculating availability based on reservations
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):
| Reservation | Room | Start | End |
| 1 | A | 01-12-2019 9:00 | 02-12-2019 4:00 |
| 2 | B | 02-12-2019 7:00 | 02-12-2019 15:00 |
| 3 | A | 02-12-2019 6:00 | 02-12-2019 23:00 |
| 4 | B | 03-12-2019 8:00 | 03-12-2019 16:00 |
| 5 | B | 03-12-2019 17:00 | 04-12-2019 10:00 |
| 6 | A | 03-12-2019 2:00 | 03-12-2019 6:00 |
| 7 | A | 03-12-2019 15:00 | 03-12-2019 20: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.
So for room A:
| Start | End | Status | Duration (hours) |
| 01-12-2019 9:00 | 02-12-2019 4:00 | Occupied | 19 |
| 02-12-2019 4:00 | 02-12-2019 6:00 | Available | 2 |
| 02-12-2019 6:00 | 02-12-2019 23:00 | Occupied | 17 |
| 02-12-2019 23:00 | 03-12-2019 2:00 | Available | 3 |
| 03-12-2019 2:00 | 03-12-2019 6:00 | Occupied | 4 |
| 03-12-2019 6:00 | 03-12-2019 15:00 | Available | 9 |
| 03-12-2019 15:00 | 03-12-2019 20:00 | Occupied | 5 |
So on 01-12-2019, you get 1 reservation that lasts until the next day at 4:00. Conditions have not been met.
.... on 02-12-2019, you get 1 reservation from 6:00-23:00, after which there's availability of 3 hours until the next reservation so all conditions have been met once.
.... on 03-12-2019, you get 2 reservations, first from 2:00-6:00 and then from 15:00-20:00; this means there was a reservation going on at/after 5:00, resulting in all availibilities greater than 1.5 hours to become valid. In between the reservations there is a gap of 9 hours; that's the first 'gap' that meets all conditions. The second gap is after 20:00 with no following reservation known, one can assume that there was basically infinite availability after that, so at least 1.5 hours.
With the example above, you would get:
| 01-12-2019 | 02-12-2019 | 03-12-2019 | 04-12-2019 | |
| A | 0 | 1 | 2 | 0 |
| B | 0 | 1 | 0 | 1 |
Any idea on how to create something like this?
Your help is very much appreciated. 🙂
7 Replies
If you imagine the computer screen when you're picking a time for a reservation it shows all the hours for all the days, with the hours already reserved in some color.
For this problem I think you will need a data structure that has all the hours in all the days and which reservations occupy each hour (or half hour, or 15 min).
You need the records for the "empty" hours because you want to query for them to find your 1.5 hours that are not occupied.
If the system that generates your data cannot produce the data in that kind of format then it will be much easier to reformat the data in excel or SQL that to try and do all the necessary calculations inside power bi.
I can help with the transformation stage if you cannot change the data you get in the extract stage.Hi kentyler! Thank you for your answer. Unfortunately, I just have this extract in excel so there's no possibility to query it via SQL.
So from what I understand, you would like to use something like:
Date/time Room Reservation 01-12-2019 9:00 A 1 01-12-2019 9:15 A 1 ... ... ... 02-12-2019 4:00 A 1 02-12-2019 4:15 A None And so forth, also for Room B?
I thought something like this would work, just by adding the 'available' rows and analyzing those lines:
Start End Status Duration (hours) 01-12-2019 9:00 02-12-2019 4:00 Occupied 19 02-12-2019 4:00 02-12-2019 6:00 Available 2 02-12-2019 6:00 02-12-2019 23:00 Occupied 17 02-12-2019 23:00 03-12-2019 2:00 Available 3 03-12-2019 2:00 03-12-2019 6:00 Occupied 4 03-12-2019 6:00 03-12-2019 15:00 Available 9 03-12-2019 15:00 03-12-2019 20:00 Occupied 5 Would be great if you can help me with this transformation (in excel?). Thank you!
Hi Anonymous ,
Here we go.
Measure = VAR time = TIME ( 5, 0, 0 ) VAR dat = MAX ( 'Table 2'[Date] ) VAR ymd = YEAR ( dat ) * 1000000 + MONTH ( dat ) * 100 + DAY ( dat ) VAR dt = dat + time VAR ro = MAX ( 'Table 2'[Room] ) VAR t = TIME ( 23, 59, 59 ) VAR lastd = dat + t VAR aa = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', ( dt > 'Table'[Start] && + DATEDIFF ( 'Table'[End], lastd, MINUTE ) > 90 && 'Table'[Room] = ro && 'Table'[End] < lastd && FORMAT ( 'Table'[Start], "yyyymmdd" ) = FORMAT ( dat, "yyyymmdd" ) ) ) ) VAR bb = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', ( dt < 'Table'[Start] && ( DATEDIFF ( dt, 'Table'[Start], MINUTE ) + DATEDIFF ( 'Table'[End], lastd, MINUTE ) ) > 90 && 'Table'[Room] = ro && 'Table'[End] < lastd && FORMAT ( 'Table'[Start], "yyyymmdd" ) = FORMAT ( dat, "yyyymmdd" ) ) ) ) VAR cc = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Room] = ro && 'Table'[End] > dt && ( YEAR ( 'Table'[Start] ) * 1000000 + MONTH ( 'Table'[Start] ) * 100 + DAY ( 'Table'[Start] ) ) <= ymd && ( YEAR ( 'Table'[End] ) * 1000000 + MONTH ( 'Table'[End] ) * 100 + DAY ( 'Table'[End] ) ) >= ymd && FORMAT ( 'Table'[Start], "yyyymmdd" ) <> FORMAT ( dat, "yyyymmdd" ) ) ) RETURN aa + bb + ccPbix as attached.
Hi v-frfei-msft! Holy moly, that looks like a lot of work. Thank you!
I was playing around with your PBIX and I had to change some of the data to explain why it's not giving me exactly what I want..:
Here I have 5 reservations for Room A on a single day.
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 instead of 4 (which is the result I'm getting now), it should be '2'!
The first one being between reservation 4 & 5. The second one being after 5.
Not sure where the '4' in the matrix above comes from. What do you think?
Hi Anonymous ,
Seemed your date columns are in text type. How about changing them to date-time?
