Occupancy based on time in / time out

[HopkiJ]

Good afternoon

I am trying to calculation occupancy for a building based on time in / time out. Ideally this would be presented as a line graph showing the occupancy over a day (using date sliders from the date table). I have tried various different solutions withi the forums but non seem to provide the answer required. Could anyone help as to how to create occupancy over time please? I have a seperate Time Table with the time dimensions and a Date Table in the model. 

Ideally time in would be +1 and time out -1 to work with the other filters in the model (I could be totally wrong).

Any help would be much appreciated!

Date	Person	Time In	Time Out
01/01/2023	Mr A	09:13:12	17:45:01
02/01/2023	Mrs L	10:04:03	13:02:09
01/01/2023	Bob	08:24:31	16:04:28

[HopkiJ · Accepted solution]

Regrettably that didn't work for me (as It only showed one total) however, I just unpivoted the column and did a conditional formula is time in then 1 and if time out then -1 and that worked. 

Many thanks

Jeni 

[BA_Pete]

It's supposed to only show one total as you said you wanted to present the output as a line graph.

If you put my measure into a line graph with your TimeTable[Time] column (or any other column from your time table) as the axis, it will show you the occupancy as at the end of the time period on the axis.

Pete

[BA_Pete]

Hi @HopkiJ 

Can't give a perfect answer without seeing your time table etc. but the basic measure structure for this would be something like this:

_timeOccupancy =
VAR __cTime = MAX(TimeTable[Time])
RETURN
CALCULATE(
    DISTINCTCOUNT(FactTable[Person]),
    KEEPFILTERS(__cTime >= FactTable[Time In]),
    KEEPFILTERS(__cTime <= FactTable[Time Out])
)

Pete