Time between start and stop from previous step

[Anonymous]

Hi!

I have investigated heavely in the forum, but have not been able to solve my issue.

I am using the following data set and need to calculate the time between two operations.

From when the previous ended till the next one start. The different orders can have a different variety of steps, but the Jobid can always be sorted to know the correct order:

I have tried to highlight the date times i want to calculate the difference between, on the first line for each modulerefid i expect to be 0.

[johnt75 · Accepted solution]

You could do this in a calculated column, but there are is a caveat. If you know that the previous job will always finish before the current one starts then

Time between jobs =
var currentStartTime = 'Table'[Start Time]
var currentModule = 'Table'[ModuleRefID]
var prevEndTime = CALCULATE( MAX( 'Table'[End Time]), 'Table'[ModuleRefID] = currentModule, 
    'Table'[End Time] < currentStartTime )
return DATEDIFF( prevEndTime, currentStartTime, MINUTE )

You can change the granularity of the DATEDIFF to whatever you need.

[johnt75 · Accepted solution]

You could do this in a calculated column, but there are is a caveat. If you know that the previous job will always finish before the current one starts then

Time between jobs =
var currentStartTime = 'Table'[Start Time]
var currentModule = 'Table'[ModuleRefID]
var prevEndTime = CALCULATE( MAX( 'Table'[End Time]), 'Table'[ModuleRefID] = currentModule, 
    'Table'[End Time] < currentStartTime )
return DATEDIFF( prevEndTime, currentStartTime, MINUTE )

You can change the granularity of the DATEDIFF to whatever you need.

[Anonymous]

Hi

I have just tested your solution but i do not get any values.

[johnt75 · Accepted solution]

Try changing the prev end time line to

var prevEndTime = CALCULATE( MAX( 'Table'[End Time]), REMOVEFILTERS('Table'),
    'Table'[ModuleRefID] = currentModule, 
    'Table'[End Time] < currentStartTime )