Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.