Calculate amount of time with motion sensors triggered

[maxesse]

Hi there, I'm trying to solve a problem with Power BI, which involves summing up how long was the motion sensor triggered in each room. Ideally, what I'd try to do is to do a datediff from the first 'motion detected' event until the corresponding 'no motion detected' for the same room, and sum them all up, but I'm pretty new to Power BI and I got stumped.

Here's a screenshot of what the data looks like:

I'd appreciate any pointers, thanks a lot,

Max

[Greg_Deckler]

This is a problem that involves EARLIER. Take a look at this article...See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Also, you might have some luck with these two quick measures that I created for dealing with durations/aggregations of time:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

If none of that helps, please post sample/example data that can be copied and pasted. 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

[maxesse]

Thanks a lot Greg, should have read the topic you pointed out before pasting a screenshot. I'm going to read the docs on EARLIER now 🙂

This is a portion of my data, if it helps:

Sensor ID	Sensor Name	Sensor Type	Value	Processed Time
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 22:54:38
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 22:54:38
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 22:55:41
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 22:56:06
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 22:56:13
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 22:56:26
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 22:57:57
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 22:58:14
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:00:51
StudioSensormotion	Studio Sensor	motion	motion detected	01/08/2018 23:01:00
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 23:01:07
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:01:12
StudioSensormotion	Studio Sensor	motion	no motion detected	01/08/2018 23:01:20
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 23:02:12
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:02:13
powerMeter	Bedroom Lamps	power	0.0	01/08/2018 23:02:24
BedroomLampsswitch	Bedroom Lamps	switch	off	01/08/2018 23:02:24
BedroomLampsswitch	Bedroom Lamps	switch	off	01/08/2018 23:02:24
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 23:02:26
BedroomSonosswitch	Bedroom Sonos	switch	off	01/08/2018 23:04:52
BedroomSonosswitch	Bedroom Sonos	switch	on	01/08/2018 23:04:53
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:06:07
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 23:06:35
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:25:15
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 23:25:30
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:25:55
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 23:26:07
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:31:02
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 23:31:27
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:31:45
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 23:31:58
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:32:18
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 23:32:32
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:32:59
BedroomSensormotion	Bedroom Sensor	motion	no motion detected	01/08/2018 23:33:10
BedroomSensormotion	Bedroom Sensor	motion	motion detected	01/08/2018 23:33:33

[v-huizhn-msft]

Hi @maxesse,

In your scenario, you need to do a datediff from the first 'motion detected' event until the corresponding 'no motion detected' for the same room, and sum them all up. In the following screenshot, get the datediff of two date in blue/red/black/green line, then sum them up. But there is no regular rule to identify the next date of  'no motion detected', so it's hard to get the result using DAX.

 

Thanks,
Angelia