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

The 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.

Reply
lememins
Regular Visitor

Need Help - Calculating duration between two events

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! 

DateTimeTypeDescriptionFactoryAreaZoneMachineReasonDateTimeIndexStop TimeDateTime.1Duration
20-Jun-1900:06:12EventF4|Setting|Area H||Area UnavailableF4SettingArea H Area Unavailable20/06/2019 00:06677020/06/2019 00:06 
20-Jun-1900:06:12EventF4|Setting|Area J||Area UnavailableF4SettingArea J Area Unavailable20/06/2019 00:06678020/06/2019 00:06 
20-Jun-1900:06:12EventF4|Setting|Area L||Area UnavailableF4SettingArea L Area Unavailable20/06/2019 00:06679020/06/2019 00:06 
20-Jun-1900:06:56EventF4|Setting|Area L|H40|Motor FaultF4SettingArea LH40Motor Fault20/06/2019 00:06676020/06/2019 00:06 
20-Jun-1900:07:00EventF4|Setting|Area J|H40|Motor FaultF4SettingArea JH40Motor Fault20/06/2019 00:07675020/06/2019 00:0700:01
20-Jun-1900:07:01EventF4|Setting|Area H|H40|Motor FaultF4SettingArea HH40Motor Fault20/06/2019 00:07674020/06/2019 00:07 
20-Jun-1900:07:49EventF4|Setting|Area H||Area UnavailableF4SettingArea H Area Unavailable20/06/2019 00:07673020/06/2019 00:07 
20-Jun-1900:08:01EventF4|Setting|Area H|J MAIN AIR SUPPLY|Layer ProblemF4SettingArea HJ MAIN AIR SUPPLYLayer Problem20/06/2019 00:08672020/06/2019 00:08 
20-Jun-1900:12:24EventF4|Press|Area K||Area UnavailableF4PressArea K Area Unavailable20/06/2019 00:12671020/06/2019 00:12 
20-Jun-1900:16:06EventF4|Press|Area K|K50|Suction Head InsertF4PressArea KK50Suction Head Insert20/06/2019 00:16670020/06/2019 00:1600:04

 

1 ACCEPTED SOLUTION
MattAllington
Community Champion
Community Champion

Check out my video here. https://m.youtube.com/watch?v=xN2IRXQ2CvI



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

View solution in original post

2 REPLIES 2
MattAllington
Community Champion
Community Champion

Check out my video here. https://m.youtube.com/watch?v=xN2IRXQ2CvI



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Thank you! 

Managed to apply the theory and get what I wanted 🙂

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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