Forum Discussion

CMoppet's avatar
CMoppet
Icon for Helper IV rankHelper IV
3 years ago

Measuring Duration between dates across two rows of data (potentially!) with conditions

I am creating a PBI report for a Field Service operation in our business.  I have one metric left that I am really struggling to figure out.  I would like to measure the end-to-end time taken in working hours for a Work Order to be completed.

 

If a machine breaks down, technicians receive a Work Order, categorised as a ‘Repair’.  If they repair the machine on that first visit, the end-to-end time is simply a count of the number of hours from the moment the Work Order was created, to the moment it is completed. 

 

However, if the technician is unable to fix the machine on that first visit, he closes the ‘Repair’ Work Order, and immediately creates a ‘Revisit’ Work Order.  To measure the end-to-end time here, I need to calculate the number of working hours from when the first ‘Repair’ Work Order was created, to the date/time when the ‘Revisit’ Work Order is closed.   

 

I need BI to look at the completion date of a ‘Repair Order’ and see if a ‘Revisit’ Work Order has been created on the same day for the same machine serial number.  If it has, the End-to-end should calculate hours from the start of the Repair to the completion of the Revisit.  If there isn’t a Revisit created on the same day, the End-to-end should just be calculated on the creation and completion of the Repair.

I think I might need one column to give a 'TRUE/FALSE' if a Revisit exists, and then a second column that measures end-to-end, according to the value in the first column.

 

Working hours are 08:30-17:00 Monday to Friday

The type of Work Order is determined in a column called ‘WO Type’

The serial number of the machine is in a column called ‘Serial Number’

The date fields are ‘Created Date & Time’ and ‘Actual End’

 

I know it’s a bit of a messy one, but any help would be much appreciated.  Thank you!