Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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.
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/
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |