Calculating people in a building based on arrival & leave times

[fetster]

Hi. I have a table for admission tickets that shows Admission Time, Exit Time, Number of Bookings. 

I would like to calculate the number of bookings that were in the building at any one time (In Building Bookings). I have produced the desired result in Excel by manually calculating the times that overlap but I would not know where to start replicating this properly in Power BI

Example - At 11:40 I would like to know how many bookings are in the building. So the calculations should total the number of bookings due to arrive upto and including 11:40 less the total number of bookings that have an exit time upto 11:40. 

The calculation would be (6+4+2+1+1+5+5+2+7+1-6-4-2) =  22 Bookings in the building.

Admission Time	Exit Time	Bookings	In Building Bookings
09:30:00	11:30	6	6
09:35:00	11:35	4	10
09:40:00	11:40	2	12
09:45:00	11:45	1	13
09:50:00	11:50	1	14
09:55:00	11:55	5	19
10:00:00	12:00	5	24
10:05:00	12:05	2	26
11:35:00	13:35	7	23
11:40:00	13:40	1	22
12:00:00	14:00	6	16
12:10:00	14:10	2	16
13:35:00	15:35	6	15

The above table assumes that people will visit for exactly 2 hours and will turn up at 5 min intervals which is correct for the ticket issuing side of things but I will also have to run a similar calculation to take into account actual arrival & leaving times whcih will not be exactly 2 hours duration and will not be rounded to nearest 5 min. 

Thanks for reading this question and I hope I have posted it in the correct place. 

[AlB]

@fetster 

You can create an auxiliary time table to place in the x-axis of the chart and then a measure:

Measure =
VAR currentTime_ =
    SELECTEDVALUE ( TimeT[Time] )
VAR last_ =
    CALCULATE (
        LASTNONBLANK ( TimeT[Time], CALCULATE ( MAX ( Table1[In Building Bookings] ) ) ),
        TimeT[Time] <= currentTime_
    )
RETURN
    CALCULATE ( DISTINCT ( Table1[In Building Bookings] ), TimeT[Time] = last_ )

 Actually if you want to do this it would have been better to create a measure from the beginning (rather than the calculated column we created earlier). See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[fetster]

Thanks for all your replies works a treat- i owe you all a beer !!! I joined the booking date with the Arrival & Leave times so I could apply the calculation to each day 

One additional question please - 
The table is only showing arrival times that have arriving bookings associated with them (10:10, 10:15 ....) are not shown in the table as there are no relevant bookings. I want to produce a barchart spanning the whole day (from 09:30 to 18:00) that will show the Bookings In Building data for each 5 min interval from 09:30 - 18:00 rather than just the times that have bookings.

Is this possible? 

[littlemojopuppy]

@fetster mmmm...beer!  😉

Here's a link for how to create a time table in Power Query.  Listen to @AlB...they know what they're doing.  Has helped me many a time 😁

[littlemojopuppy]

@fetster  use @AlB suggestion...simple and elegant!  😉

[AlB · Accepted solution]

@fetster 

In fact, I overcomplicated it on my previous post. It can be simpler:

Column V2 =
VAR currentAdm_ = Table1[Admission Time]
RETURN
    CALCULATE (
        SUM ( Table1[Bookings] ),
        Table1[Admission Time] <= currentAdm_,
        Table1[Exit Time] > currentAdm_,
        ALL ( Table1 )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @fetster 

If you want a calculated column in your table, as you seem to show:

Column =
VAR currentAdm_ = Table1[Admission Time]
RETURN
    CALCULATE (
        SUMX (
            SUMMARIZE (
                Table1,
                Table1[Admission Time],
                Table1[Exit Time],
                Table1[Bookings]
            ),
            Table1[Bookings]
        ),
        Table1[Admission Time] <= currentAdm_,
        Table1[Exit Time] > currentAdm_,
        ALL ( Table1 )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[littlemojopuppy]

Good morning!

Here are three measures...

Admissions = 
    CALCULATE(
	SUM(Bookings[Bookings]),
	Bookings[AdmissionDate] = TODAY(),
	FILTER(
            ALL(Bookings),
            Bookings[AdmissionTime] <= SELECTEDVALUE(Bookings[AdmissionTime])
        )
    )

Departures = 
    CALCULATE(
	SUM(Bookings[Bookings]),
	Bookings[ExitDate] = TODAY(),
	FILTER(
            ALL(Bookings),
            Bookings[ExitTime] <= SELECTEDVALUE(Bookings[AdmissionTime])
        )
    )

Bookings on Site = [Admissions] - [Departures]

I added admission and exit date fields because I assume you'll be tracking this over time.  You should probably change TODAY() in each of these measures to SELECTEDVALUE(Bookings[AdmissionDate]) so they'll work across dates instead of just today.

[amitchandak]

@fetster , you need to have independent time table for slicer , then on selected time

new measure

calculate(sum(Table[bookings]), filter(Table, Table[Admission Time]<= selectedvalue(Time[Time]) && Table[Exit Time] >= selectedvalue(Time[Time])))

[Fowmy]

@fetster 

Add the following code as a column to your table:

Building Bookings = 
var _admin = 'Table 3'[Admission Time]

return


CALCULATE(
    SUM('Table 3'[Bookings]),
    'Table 3'[Admission Time] <= _admin,
    ALL('Table 3')
) - 
CALCULATE(
    SUM('Table 3'[Bookings]),
    'Table 3'[Exit Time] <= _admin,
    ALL('Table 3')
)

If you need a measure then:

Building Bookings Measure = 
var _admin = MAX('Table 3'[Admission Time])

return


CALCULATE(
    SUM('Table 3'[Bookings]),
    'Table 3'[Admission Time] <= _admin,
    ALL('Table 3')
) - 
CALCULATE(
    SUM('Table 3'[Bookings]),
    'Table 3'[Exit Time] <= _admin,
    ALL('Table 3')
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn