Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.