Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi all,
I need help to calculate the time between two events.
Our events come through with Factory | Area | Zone | Machine | Reason
so when an area stops it always has Reason = Machine Unavailable, when an area restarts a machine and reason is entered
Stop: F4|Setting|Area J||Area Unavailable
Start: F4|Setting|Area J|H40|Motor Fault
I want ot calculate the duration between the two timestamps - I can't calculate previous rows as there are multiple areas / zones that could stop in the same table.
I've highlighted two examples in the table below where I've manually put in the duration but haven't figured out how to do this automatically. Hopefully then I can create visuals which display stop duration by machine, by area, by date etc...
Thanks!
Date | Time | Type | Description | Factory | Area | Zone | Machine | Reason | DateTime | Index | Stop Time | DateTime.1 | Duration |
20-Jun-19 | 00:06:12 | Event | F4|Setting|Area H||Area Unavailable | F4 | Setting | Area H | Area Unavailable | 20/06/2019 00:06 | 677 | 0 | 20/06/2019 00:06 | ||
20-Jun-19 | 00:06:12 | Event | F4|Setting|Area J||Area Unavailable | F4 | Setting | Area J | Area Unavailable | 20/06/2019 00:06 | 678 | 0 | 20/06/2019 00:06 | ||
20-Jun-19 | 00:06:12 | Event | F4|Setting|Area L||Area Unavailable | F4 | Setting | Area L | Area Unavailable | 20/06/2019 00:06 | 679 | 0 | 20/06/2019 00:06 | ||
20-Jun-19 | 00:06:56 | Event | F4|Setting|Area L|H40|Motor Fault | F4 | Setting | Area L | H40 | Motor Fault | 20/06/2019 00:06 | 676 | 0 | 20/06/2019 00:06 | |
20-Jun-19 | 00:07:00 | Event | F4|Setting|Area J|H40|Motor Fault | F4 | Setting | Area J | H40 | Motor Fault | 20/06/2019 00:07 | 675 | 0 | 20/06/2019 00:07 | 00:01 |
20-Jun-19 | 00:07:01 | Event | F4|Setting|Area H|H40|Motor Fault | F4 | Setting | Area H | H40 | Motor Fault | 20/06/2019 00:07 | 674 | 0 | 20/06/2019 00:07 | |
20-Jun-19 | 00:07:49 | Event | F4|Setting|Area H||Area Unavailable | F4 | Setting | Area H | Area Unavailable | 20/06/2019 00:07 | 673 | 0 | 20/06/2019 00:07 | ||
20-Jun-19 | 00:08:01 | Event | F4|Setting|Area H|J MAIN AIR SUPPLY|Layer Problem | F4 | Setting | Area H | J MAIN AIR SUPPLY | Layer Problem | 20/06/2019 00:08 | 672 | 0 | 20/06/2019 00:08 | |
20-Jun-19 | 00:12:24 | Event | F4|Press|Area K||Area Unavailable | F4 | Press | Area K | Area Unavailable | 20/06/2019 00:12 | 671 | 0 | 20/06/2019 00:12 | ||
20-Jun-19 | 00:16:06 | Event | F4|Press|Area K|K50|Suction Head Insert | F4 | Press | Area K | K50 | Suction Head Insert | 20/06/2019 00:16 | 670 | 0 | 20/06/2019 00:16 | 00:04 |
Solved! Go to Solution.
Check out my video here. https://m.youtube.com/watch?v=xN2IRXQ2CvI
Check out my video here. https://m.youtube.com/watch?v=xN2IRXQ2CvI
Thank you!
Managed to apply the theory and get what I wanted 🙂 !
User | Count |
---|---|
143 | |
71 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |