Forum Discussion

GScott32's avatar
GScott32
New Member
5 years ago
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 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:

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      GScott32
      New Member
      5 years ago

      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: 

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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