cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Llewbear Regular Visitor
Regular Visitor

Time Spent in each room

Good day,

 

I have a similar to THIS POST, where I'm also trying to address with ALLEXCEPT(), however mine has a little twist. 

 

I'm trying to answer the following questions:

 

1 What room is the person in (Assuming there is only one person in the house)? 

2 How long has he/she been in this room?

3. Are they active or idle within this room (count of sensor activity)?

 

I thought that 1 may be solved with some timestamped calculated column that updates when a motion sensor triggers in a new room. For 2 Might be the DateDiff between first and last time the sensor triggered and 3 Might be a simple Count of motion sensor events during the period.

 

So the logic I was trying to follow was to calculate the MIN and MAX of the DateTime for each room, from each location (there are more than one location in the data and then measuring the difference between the min and max times.

 

NB!! This approach unfortunately doesn't take into account, the time spent when moving from one room to another.

 

When I use the calculation: Motion Sensor MIN = CALCULATE(MIN ( Motion[Time(no secs)]), ALLEXCEPT ( Motion, Motion[Date], Motion[Location], Motion[Room] )), it does not take into account when a person goes from a Living Room to a Bathroom back to the Living Room.

 

My data is as follows:

RoomLocationStatusDateDateTimeTimeTime(no secs)
Living RoomLocation 1active2018/05/162018/05/16 00:1400:14:3500:14
Living RoomLocation 1active2018/05/162018/05/16 00:3100:31:0900:31
Living RoomLocation 1inactive2018/05/162018/05/16 17:4317:43:0417:43
Living RoomLocation 1inactive2018/05/162018/05/16 18:5218:52:4718:52
Living RoomLocation 1active2018/05/162018/05/16 19:3819:38:5219:38
Living RoomLocation 1inactive2018/05/162018/05/16 20:5320:53:0720:53
Living RoomLocation 1active2018/05/162018/05/16 21:1921:19:3021:19
FL6 BathroomLocation 1inactive2018/05/162018/05/16 21:3321:33:3821:33
FL6 BathroomLocation 1inactive2018/05/162018/05/16 21:3721:37:5021:37
Living RoomLocation 1inactive2018/05/162018/05/16 23:4523:45:1523:45
Living RoomLocation 1inactive2018/05/172018/05/17 10:0010:00:2410:00
Living RoomLocation 1active2018/05/172018/05/17 10:3410:34:2810:34
Living RoomLocation 1inactive2018/05/172018/05/17 11:0211:02:0811:02
Living RoomLocation 1active2018/05/172018/05/17 11:0911:09:5511:09
Living RoomLocation 1active2018/05/172018/05/17 11:2611:26:2611:26
Living RoomLocation 1active2018/05/172018/05/17 12:1612:16:3912:16
Living RoomLocation 1active2018/05/172018/05/17 12:2312:23:1312:23
Living RoomLocation 1inactive2018/05/172018/05/17 14:4514:45:0614:45
Living RoomLocation 1inactive2018/05/172018/05/17 15:5915:59:2315:59
Living RoomLocation 1active2018/05/172018/05/17 16:2116:21:0116:21
Living RoomLocation 1inactive2018/05/172018/05/17 17:2917:29:2017:29
Living RoomLocation 1inactive2018/05/172018/05/17 18:0018:00:3418:00
Living RoomLocation 1active2018/05/172018/05/17 18:0018:00:1818:00
Living RoomLocation 1inactive2018/05/172018/05/17 19:2119:21:3819:21
Living RoomLocation 1inactive2018/05/172018/05/17 20:4320:43:4320:43
Living RoomLocation 1active2018/05/172018/05/17 21:4121:41:3621:41
Living RoomLocation 1active2018/05/172018/05/17 21:5721:57:5921:57
Living RoomLocation 1active2018/05/172018/05/17 22:1722:17:4722:17
Living RoomLocation 1active2018/05/182018/05/18 08:1108:11:0208:11
Living RoomLocation 1active2018/05/182018/05/18 08:4608:46:5308:46
Living RoomLocation 1inactive2018/05/182018/05/18 09:0809:08:5909:08
Living RoomLocation 1active2018/05/182018/05/18 10:0610:06:1810:06
Living RoomLocation 1active2018/05/182018/05/18 11:0011:00:0211:00
Living RoomLocation 1active2018/05/182018/05/18 11:5111:51:1811:51
Living RoomLocation 1inactive2018/05/182018/05/18 11:5811:58:2611:58
Living RoomLocation 1inactive2018/05/182018/05/18 16:2616:26:4916:26
Living RoomLocation 1active2018/05/182018/05/18 18:4418:44:4718:44
Living RoomLocation 1inactive2018/05/182018/05/18 18:5018:50:3718:50
Living RoomLocation 1inactive2018/05/182018/05/18 18:5718:57:2018:57
Living RoomLocation 1inactive2018/05/182018/05/18 19:0019:00:1719:00
Living RoomLocation 1inactive2018/05/182018/05/18 19:0419:04:1719:04
Living RoomLocation 1inactive2018/05/182018/05/18 19:0919:09:4619:09
FL6 BathroomLocation 1inactive2018/05/182018/05/18 21:1621:16:4521:16
FL6 BathroomLocation 1active2018/05/182018/05/18 21:2121:21:2321:21
Living RoomLocation 1inactive2018/05/192018/05/19 01:0901:09:4801:09
Living RoomLocation 1inactive2018/05/192018/05/19 01:1601:16:0501:16
Living RoomLocation 1inactive2018/05/192018/05/19 01:2201:22:1401:22
Living RoomLocation 1inactive2018/05/192018/05/19 10:0810:08:0710:08
Living RoomLocation 1inactive2018/05/192018/05/19 10:2810:28:3810:28
Living RoomLocation 1active2018/05/192018/05/19 10:2810:28:2610:28
Living RoomLocation 1inactive2018/05/192018/05/19 14:3714:37:3314:37
Living RoomLocation 1inactive2018/05/192018/05/19 14:5914:59:1314:59
Living RoomLocation 1inactive2018/05/192018/05/19 17:5917:59:0417:59
Living RoomLocation 1inactive2018/05/192018/05/19 18:2418:24:3918:24
Living RoomLocation 1inactive2018/05/192018/05/19 20:3920:39:3220:39
Living RoomLocation 1active2018/05/192018/05/19 22:0222:02:0322:02
Living RoomLocation 1active2018/05/192018/05/19 23:4523:45:2823:45

 

Any guidance you can give in this regard would be very much appreciated!!

 

Thank you and have a wonderful day.

 

Kind Regards,

LlewBear

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Time Spent in each room

I assumed that travel time is only relevant if done the same day. If you remove the criteria of the same day, then it will work across days as well e.g. night 18/19 of May

Travel Time =
VAR CurrentRoom = Motion[Room]
VAR CurrentLocation = Motion[Location]
VAR CurrentDate = Motion[Date]
VAR CurrentTimeStamp = Motion[DateTime]
VAR PreviousTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        ALL ( Motion ),
        Motion[DateTime] < CurrentTimeStamp
    )
VAR PreviousRoomTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        FILTER (
            ALL ( Motion ),
            Motion[DateTime] = PreviousTimeStamp
                && Motion[Location] = CurrentLocation
                && Motion[Room] <> CurrentRoom
        )
    )
RETURN
    IF (
        PreviousRoomTimeStamp <> BLANK (),
        CurrentTimeStamp - PreviousRoomTimeStamp
    )
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

10 REPLIES 10
Super User II
Super User II

Re: Time Spent in each room

regarding 2) - can you add calculated column for the travel time, and then subtract it from the time in the room formula?

Travel Time = 
VAR CurrentRoom = Motion[Room]
VAR CurrentLocation = Motion[Location]
VAR CurrentDate = Motion[Date]
VAR CurrentTimeStamp = Motion[DateTime]
VAR PreviousTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        ALL ( Motion ),
        Motion[DateTime] < CurrentTimeStamp
    )
VAR PreviousRoomTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        FILTER (
            ALL ( Motion ),
            Motion[Date] = CurrentDate
                && Motion[DateTime] = PreviousTimeStamp
                && Motion[Location] = CurrentLocation
                && Motion[Room] <> CurrentRoom
        )
    )
RETURN
    IF (
        PreviousRoomTimeStamp <> BLANK (),
        CurrentTimeStamp - PreviousRoomTimeStamp
    )



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Microsoft v-jiascu-msft
Microsoft

Re: Time Spent in each room

Hi LlewBear,

 

To be honest, the calculations here could be easy. The difficult part could be the logic. Can you share us the steps to solve this with just one day's data? They are all date time points rather than time durations. So how to get the results by plain math formula. Then we can convert it into DAX formula.

 

Room Location Status Date DateTime Time Time(no secs)
Living Room Location 1 active 2018/05/16 2018/05/16 00:14 00:14:35 00:14
Living Room Location 1 active 2018/05/16 2018/05/16 00:31 00:31:09 00:31
Living Room Location 1 inactive 2018/05/16 2018/05/16 17:43 17:43:04 17:43
Living Room Location 1 inactive 2018/05/16 2018/05/16 18:52 18:52:47 18:52
Living Room Location 1 active 2018/05/16 2018/05/16 19:38 19:38:52 19:38
Living Room Location 1 inactive 2018/05/16 2018/05/16 20:53 20:53:07 20:53
Living Room Location 1 active 2018/05/16 2018/05/16 21:19 21:19:30 21:19
FL6 Bathroom Location 1 inactive 2018/05/16 2018/05/16 21:33 21:33:38 21:33
FL6 Bathroom Location 1 inactive 2018/05/16 2018/05/16 21:37 21:37:50 21:37
Living Room Location 1 inactive 2018/05/16 2018/05/16 23:45 23:45:15 23:45

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Llewbear Regular Visitor
Regular Visitor

Re: Time Spent in each room

Good day Stachu, 

 

Thank you for the info. I've tried this formula but it returns no result. I understand the logic you are putting across but it doesn't seem to work.

 

Thanks!

Llewbear Regular Visitor
Regular Visitor

Re: Time Spent in each room

Good day v-jiascu-msft,

 

Let me try put the logic across.

 

There is one person living in this house.

As per my previous message, I'm trying to answer 2 main questions - let me try ellobrate:

 

1. How much time is spent by this person in each room everyday (not aggregated by day but by time period in room). So I want to be able to calculate how much time he/she spent from the time when the first motion sensor triggered for the day (First time stamp for the day) in a particular room. How much time passes while this person is in that room? Then when the person moves from one room to another, it should again calculate the difference between the first time stamp for the sensor of the new room and the first sensor trigger of the next room.

2. While a person is in a particular room, the sensor goes off every time a person moves - here I want to know hw many times the sensor was activated while the person was in that room for that specific period of time. 

 

Logic 1

So my logic is telling me that for 1 above I need to find the min and max time the person spent in each room within a period of time. The period of time being the first time the sensor went off and that room, and the first time the sensor went off in another room when the person moved to a new room.

 

Logic 2

For 2 above, it should just be the count of how many times the sensor was triggered while in the room between the min and max as defined in Logic 1 above.

 

I hope this makes more sense.

 

Thanks again.

Super User II
Super User II

Re: Time Spent in each room

Hi
have you created calculated column? This syntax will not work as a measure
see the example file here
https://1drv.ms/u/s!AjxUGXgGNzCEiVPvfHe5L_OYRkCx

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Llewbear Regular Visitor
Regular Visitor

Re: Time Spent in each room

Hi Stachu, 

 

This is definately moving in the right direction. So what you have done is calculating the difference in time from the last timestamp in one room vs the first timestamp in the next room (i.e. travel time), which is great, but it does not seem to work across the dates for all occations - not sure why? 

 

Thanks!!

 

LlewBear

Super User II
Super User II

Re: Time Spent in each room

I assumed that travel time is only relevant if done the same day. If you remove the criteria of the same day, then it will work across days as well e.g. night 18/19 of May

Travel Time =
VAR CurrentRoom = Motion[Room]
VAR CurrentLocation = Motion[Location]
VAR CurrentDate = Motion[Date]
VAR CurrentTimeStamp = Motion[DateTime]
VAR PreviousTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        ALL ( Motion ),
        Motion[DateTime] < CurrentTimeStamp
    )
VAR PreviousRoomTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        FILTER (
            ALL ( Motion ),
            Motion[DateTime] = PreviousTimeStamp
                && Motion[Location] = CurrentLocation
                && Motion[Room] <> CurrentRoom
        )
    )
RETURN
    IF (
        PreviousRoomTimeStamp <> BLANK (),
        CurrentTimeStamp - PreviousRoomTimeStamp
    )
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Llewbear Regular Visitor
Regular Visitor

Re: Time Spent in each room

Thanks for this Stachu, this definately guided me in the right direction. 

 

I've done the following, I've said in one calculated column, where Motion[Room] <> CurrentRoom and another where Motion[Room] = CurrentRoom - then used an IF statement to return the relevant value. I can then just sum the values as required and I have question 1 answered.  Let me know if there is a cleaner way of doing this. 

 

 

Travel Time = 
VAR CurrentRoom = Motion[Room]
VAR CurrentLocation = Motion[Location]
VAR CurrentDate = Motion[Date]
VAR CurrentTimeStamp = Motion[DateTime]
VAR PreviousTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        ALL ( Motion ),
        Motion[DateTime] < CurrentTimeStamp
    )
VAR PreviousRoomTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        FILTER (
            ALL ( Motion ),
            Motion[DateTime] = PreviousTimeStamp
                && Motion[Location] = CurrentLocation
                && Motion[Room] = CurrentRoom
        )
    )
RETURN
    IF (
        PreviousRoomTimeStamp <> BLANK (),
        CurrentTimeStamp - PreviousRoomTimeStamp
    )

 

Travel Time 2 = 
VAR CurrentRoom = Motion[Room]
VAR CurrentLocation = Motion[Location]
VAR CurrentDate = Motion[Date]
VAR CurrentTimeStamp = Motion[DateTime]
VAR PreviousTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        ALL ( Motion ),
        Motion[DateTime] < CurrentTimeStamp
    )
VAR PreviousRoomTimeStamp =
    CALCULATE (
        MAX ( Motion[DateTime] ),
        FILTER (
            ALL ( Motion ),
            Motion[DateTime] = PreviousTimeStamp
                && Motion[Location] = CurrentLocation
                && Motion[Room] <> CurrentRoom
        )
    )
RETURN
    IF (
        PreviousRoomTimeStamp <> BLANK (),
        CurrentTimeStamp - PreviousRoomTimeStamp
    )

And finally...

 

Time Spent =
IF (
    FIRSTDATE ( Motion[Date] ) && ISBLANK ( Motion[Travel Time] )
        && ISBLANK ( Motion[Travel Time 2] ),
    Motion[Time(no secs)],
    IF (
        ISBLANK ( Motion[Travel Time] ),
        Motion[Travel Time 2],
        Motion[Travel Time]
    )
)

And then the count of motion censor triggers

 

Activity Count =
CALCULATE (
    COUNT ( Motion[Room] ),
    ALLEXCEPT ( Motion, Motion[Date], Motion[Location], Motion[Room] )
)

Any revisions will be appreciated, else I am a happy chappie 🙂

 

Thanks again!!

 

Super User II
Super User II

Re: Time Spent in each room

maybe I am oversimplyfying, but shouldn't Time Spent be just 

Time Spent = SUM(Motion[Travel Time])

it excludes the travel time, and in case you want to add it you can just add the SUM(Motion[Travel Time 2])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors