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?
