cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MirBatman
New Member

Duration between two times in the same column - Downtime

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.

MirBatman_1-1640302785177.png

 

 

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! 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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

21122801.jpg

 

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

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

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

21122801.jpg

 

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

Thanks a lot. 

 

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

 

 CURRENT DESIRED
DateTimeColumn4ColumnColumn
10/10/2021 5:32:29 AMMachine Stopped620
10/10/2021 5:32:29 AMProduction Stopped6262
10/10/2021 5:33:31 AMMachine is running00
10/10/2021 6:33:28 AMProduction Stopped34851
10/10/2021 6:34:19 AMMachine Stopped297297
10/10/2021 6:39:16 AMMachine is running00
10/10/2021 9:17:01 AMProduction Stopped3838
10/10/2021 9:17:39 AMMachine is running00

 

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
Super User
Super User

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/

 

 

DateTimeColumn4Column
10/10/2021 5:32:29 AMMachine Stopped0
10/10/2021 5:32:29 AMProduction Stopped62
10/10/2021 5:33:31 AMMachine is running0
10/10/2021 6:33:28 AMProduction Stopped51
10/10/2021 6:34:19 AMMachine Stopped297
10/10/2021 6:39:16 AMMachine is running0
10/10/2021 9:17:01 AMProduction Stopped38
10/10/2021 9:17:39 AMMachine is running0

 

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. 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!