Calculating availability based on reservations

[Anonymous]

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

[v-frfei-msft]

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 + cc

Pbix as attached.

[Anonymous]

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?

[v-frfei-msft]

Hi @Anonymous ,

Seemed your date columns are in text type. How about changing them to date-time?

[Anonymous]

Hi @v-frfei-msft,

I'm pretty sure they are in date/time format (I actually took your PBIX and altered some of the data).. See:

[Anonymous]

Hi @v-frfei-msft, any idea what's going on in the last example I gave?

[kentyler]

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.

[Anonymous]

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!