Forum Discussion
Occupancy based on time in / time out
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 |
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
3 Replies
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
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
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
