Forum Discussion
Solved
Calculating the time difference between rows based on a boolean value for occupancy sensors
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...
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
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