Finding a value in a column, navigating to the next change in value, then perform calculation

[RichardJ]

Hi,

I've been trying without success (so far!) to calculate the machine downtime from the available data

The 'Status' of "NC Stop" means the machine has stopped at the time stated in the 'Date' column

The 'Status' of "NC Start" means the machine has started at the time stated in the 'Date' column

Thanks to some earlier help from from @JarroVGIT Calculating Machine uptime - is this possible in Power BI from the data available?

I was able to calculate the 'Time of next log entry event'/create an index/understand a good approach which was great however, having received more complete data the fact that there can be multiple 'NC Start' statuses has muddied the waters.

The part which is confusing me is that there can be multiple "NC Start" entries in the 'Status' column as the machine works its way through the different operations.

In the table below I would like to find:

Machine Stopped Time = Find the next row with a 'Status' of 'NC Stop' and note the Date value
Machine Started Time = From the row which was found, find the first next occurence of a row with a Status of 'NC Start' and note the Date Value

Machine Downtime = Machine Started Time - Machine Stopped Time

Please ignore the Total Idle Time and Time Difference columns - they are only there as I've been playing around.

The current formulas are:

Next Status Change Time = CALCULATE(MIN('OMV 1'[Date]), FILTER('OMV 1', 'OMV 1'[Status] <> EARLIER('OMV 1'[Status]) && 'OMV 1'[Date] > EARLIER('OMV 1'[Date])))

Time Difference (Seconds) = DATEDIFF('OMV 1'[Date], 'OMV 1'[Next Status Change Time], SECOND)

Am guessing that the 'Next Status Change Time' formula needs to change to find a later rather than earlier value. Have tried playing about with the parameters without success.

Any help or advice would be appreciated.

Thanks

Date	Type	Operation	Text	Attributes	Status	Stop Reason	Index	Next Status Change Time	Time Difference (Seconds)	Time Difference (Minutes)	Time of next log entry event	Time since last log entry (Second)	Total Idle Time
24/09/2019 04:37	Key			0x01F0 -> NC Start	NC Start		0	24/09/2019 07:06	8900	149	24/09/2019 04:37	1	1491688
24/09/2019 04:37	Key			0x01F0 -> NC Start	NC Start		1	24/09/2019 07:06	8899	149	24/09/2019 04:39	84	1491688
24/09/2019 04:39	Key			0x01F0 -> NC Start	NC Start		2	24/09/2019 07:06	8815	147	24/09/2019 04:39	1	1491688
24/09/2019 04:39	Key			0x01F0 -> NC Start	NC Start		3	24/09/2019 07:06	8814	147	24/09/2019 06:04	5112	1491688
24/09/2019 06:04	Key			0x01F0 -> NC Start	NC Start		4	24/09/2019 07:06	3702	62	24/09/2019 06:04	1	1491688
24/09/2019 06:04	Key			0x01F0 -> NC Start	NC Start		5	24/09/2019 07:06	3702	62	24/09/2019 06:04	1	1491688
24/09/2019 06:04	Key			0x01F0 -> NC Start	NC Start		6	24/09/2019 07:06	3702	62	24/09/2019 06:04	1	1491688
24/09/2019 06:04	Key			0x01F0 -> NC Start	NC Start		7	24/09/2019 07:06	3702	62	24/09/2019 06:04	1	1491688
24/09/2019 06:04	Key			0x01F0 -> NC Start	NC Start		8	24/09/2019 07:06	3701	62	24/09/2019 06:58	3238	1491688
24/09/2019 06:58	Key			0x01F0 -> NC Start	NC Start		9	24/09/2019 07:06	463	8	24/09/2019 07:06	463	1491688
24/09/2019 07:06	Info			MAIN	NC Stop	M02	10	24/09/2019 07:17	658	11	24/09/2019 07:17	658	1491688
24/09/2019 07:17	Key			0x01F0 -> NC Start	NC Start		11	24/09/2019 09:59	9738	162	24/09/2019 07:18	88	1491688
24/09/2019 07:17	Key			0x01F0 -> NC Start	NC Start		12	24/09/2019 09:59	9738	162	24/09/2019 07:18	88	1491688
24/09/2019 07:18	Key	OP20		0x01F0 -> NC Start	NC Start		13	24/09/2019 09:59	9650	161	24/09/2019 08:41	4948	1491688

[v-xicai · Accepted solution]

Hi @RichardJ ,

You can create columns like DAX below.

Machine Stopped Time= CALCULATE(MIN('OMV 1'[Date]), FILTER('OMV 1', 'OMV 1'[Status] ="NC Stop" && 'OMV 1'[Index] > EARLIER('OMV 1'[Index])))
 
Machine Started Time = CALCULATE(MIN('OMV 1'[Date]), FILTER('OMV 1', 'OMV 1'[Status] ="NC Start" && 'OMV 1'[Index] > EARLIER('OMV 1'[Index])))
 
Machine Downtime = [Machine Started Time] - [Machine Stopped Time]

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RichardJ]

Appreciate you pointing me in the right direction @v-xicai  - Thank you

Applied a filter to only select 'Status' of "NC Stop" (This is where I was going wrong as having superflous data in the table was confusing matters when calculating the time difference between row/events)

This column was added

Time of next NC Start entry = CALCULATE(MIN('OMV 1'[Date]), FILTER('OMV 1', 'OMV 1'[Status] <> EARLIER('OMV 1'[Status]) && 'OMV 1'[Date] > EARLIER('OMV 1'[Date])))

to find the Start time following the previous Stop.

The time difference between the NC Stop and the NC Start was found using this

Machine Idle Time (Minutes) = DATEDIFF( 'OMV 1'[Date],'OMV 1'[Time of next NC Start entry], MINUTE)

This provided me with the duration of how long the machine wasn't working which is what I was after.

Thanks again!