Calculating the time difference between rows based on a boolean value for occupancy sensors

[GScott32]

Thank in advance for any help, quite an interesting problem I have.

I have occupancy sensors in my office that return a true or false state based on when the space is occupied or not. 

If the space becomes occupied then the sensor will add a true row to the dataset and when that space becomes free or un-occupied it will add a false row. See data Table.

I need to calculate the time differnce between the true entry and the next false entry for that same sensor to tell me the total time occupied. I have seen other solutions where you calculate the differnece to the next row but wont always be the next entry for that sensor as the sensor may return enviromental data in the meantime. 

Here you can see the data for one sensor:

[Anonymous · Accepted solution]

Hi @GScott32,

You can calculate the difference between the current row content and the previous row, but power bi not support to use DAX expressions to direct add new records to current table. (you need to create a calculated table to manually add new records)

You can also try to use following formula if helps:

Measure =
VAR currDate =
    MAX ( Table[Date/Time] )
VAR currStatus =
    SELECTEDVALUE ( Table[Activity] )
VAR prevDate =
    CALCULATE (
        MAX ( Table[Date/Time] ),
        FILTER ( ALLSELECTED ( Table ), [Date/Time] < currDate && [Activity] = "TRUE" ),
        VALUES ( Table[DeviceName] )
    )
RETURN
    IF ( currStatus = "FALSE", DATEDIFF ( prevDate, curDate, SECOND ) )

Regards,

Xiaoxin Sheng

[amitchandak]

@GScott32 , Try a new column like

new column =
var _next = minx(filter(Table, [devicename] = earlier([devicename]) && [Date/time] >earlier([Date/time]) && [Activity] =false()),[Date/time])
return
if([Activity] - true(), datediff([Date/time],_next, minute))

[GScott32]

Hello, 

Thank you for the potential solution. It is very close to working.

I am having one issue with it where not all of the occupied times line up: