Duration between two times in the same column - Downtime

[MirBatman]

Hi, Good folks of Power BI. I am a new bee trying to learn and work with this fantastic tool. 

I am trying to get some downtime data out from the below data set.

I am trying to get the time difference as the formal below:

Machine Stopped - Machine is running & Production Stop - Machine is Running. Basically, any event before Machine is running - Machine is running.

Any advice or direction would be greatly appreciated

Thanks a lot! 

[v-jingzhang · Accepted solution]

Hi @MirBatman 

In case a "Machine is running" time happens on the second day after the events before it, I combine Date and Time into a DateTime column in the table for calculation. Then add a calculated column to get the duration in seconds. You can change the statement after "RETURN" to get different format per your need. 

DateTime = TestTable[Date] + TestTable[Time]

Column = 
VAR _endTime = MINX(FILTER(TestTable, TestTable[Column4] = "Machine is running" && TestTable[DateTime] >= EARLIER(TestTable[DateTime])), TestTable[DateTime])
VAR _duration = _endTime - TestTable[DateTime]  // This duration is in Day unit. 
RETURN
_duration * 24 * 60 * 60  // Convert the duration to Seconds

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[MirBatman]

Thanks a lot. 

This definitely helps. Just an add-on question, how can I get the below result?

	CURRENT	DESIRED
DateTime	Column4	Column	Column
10/10/2021 5:32:29 AM	Machine Stopped	62	0
10/10/2021 5:32:29 AM	Production Stopped	62	62
10/10/2021 5:33:31 AM	Machine is running	0	0
10/10/2021 6:33:28 AM	Production Stopped	348	51
10/10/2021 6:34:19 AM	Machine Stopped	297	297
10/10/2021 6:39:16 AM	Machine is running	0	0
10/10/2021 9:17:01 AM	Production Stopped	38	38
10/10/2021 9:17:39 AM	Machine is running	0	0

Along with the current query, which references " Machine is running" and subtracts all the earlier events. Can we get the duration between the consecutive events? 

I am trying to get total downtime on this equipment. 

Please do let me know at your convenience. 

[VahidDM]

Hi @MirBatman 

Can you add a sample of your expected result table/column?

Also, it'd be great if you share sample of your data as a text not an image.

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[MirBatman]

DateTime	Column4	Column
10/10/2021 5:32:29 AM	Machine Stopped	0
10/10/2021 5:32:29 AM	Production Stopped	62
10/10/2021 5:33:31 AM	Machine is running	0
10/10/2021 6:33:28 AM	Production Stopped	51
10/10/2021 6:34:19 AM	Machine Stopped	297
10/10/2021 6:39:16 AM	Machine is running	0
10/10/2021 9:17:01 AM	Production Stopped	38
10/10/2021 9:17:39 AM	Machine is running	0

I am trying to get the total machine downtime in seconds. I am sorry, as I do not know how to attach a sample .pbix file. I do not see an attachment option, or I do not know how to.