Forum Discussion
Time between start and stop from previous step
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.
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.
Try changing the prev end time line to
var prevEndTime = CALCULATE( MAX( 'Table'[End Time]), REMOVEFILTERS('Table'),
'Table'[ModuleRefID] = currentModule,
'Table'[End Time] < currentStartTime )
3 Replies
johnt75Super User
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
Try changing the prev end time line to
var prevEndTime = CALCULATE( MAX( 'Table'[End Time]), REMOVEFILTERS('Table'),
'Table'[ModuleRefID] = currentModule,
'Table'[End Time] < currentStartTime )