Forum Discussion

JohnCowx's avatar
JohnCowx
Frequent Visitor
6 years ago

Dynamic % utilisation

Hi

I am calculating % space utilisation of a floor and want to dynamically adjust the % utilisation based on a user changing start and end time parameters (eg 9am to 5pm).

I have successfully calculated the % utilisation so far using static defined start and end times.

1. emove or truncate event times which do not fall into the defined time period.

2. A calculated column gets the difference between the start and end of an event (someone sitting at a position): 

Event_Space_Merge[TimeSinceLastEvent]

3. A measure to sum the calculated column together across a building/ floor:
SumTimeUsed = calculate(sum(Event_Space_Merge[TimeSinceLastEvent]),Event_Space_Merge[Event.state]=0)
4. and finally the utilisation:
Utilization_Occupied = calculate([SumTimeUsed]/([NumDevices_Floor]*[MinutesInDay]))
 
The above does not work with changing parameters as the column values are fixed. I want someone to be able to select their own start and end times and the results to change dynamically.
 
So far the closest I've come using measures is I calculate the difference in time for each row through measures: 
Time_SinceLastEvent = DATEDIFF([Time_Value_Previous],[Time_Value_Current],MINUTE)
 - but when I come to sum each row's time difference SUM(Time_SinceLastEvent) I am getting an error which basically says I need a column to compute against.
 
How can I make this work to dynamically accept the parameters and sum each row's time to calculate the % utilisation?