Dynamic % utilisation

[JohnCowx]

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?

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490