Identify cycles in records

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5BCoAwDAS/IjkLzaYNhjxCBMFL6f+/YQ6lgvbgJRsWdphaSUxgoJW2zHFPLow9nuPiEgEkScIiC9gZrviU2dWorR2VB6oTBnGyepfqyg9Kf1rpzMocYdVu", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [event_id = _t, machine_id = _t, point_to_id = _t, point_from_id = _t, mission_start_time = _t, mission_end_time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"mission_start_time", type datetime}, {"mission_end_time", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Cycle Time", each try
if [point_to_id]=#"Added Index"{[Index]-1}[point_from_id]
and [point_from_id]=#"Added Index"{[Index]-1}[point_to_id]
and [mission_start_time]=#"Added Index"{[Index]-1}[mission_end_time]
then [mission_end_time]-#"Added Index"{[Index]-1}[mission_start_time] else null otherwise null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Cycle Time", type duration}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Hi Syndicate_Admin ,

1. Create a new column to rank the start time with machine group:

Rank = RANKX(FILTER('Table',EARLIER('Table'[machine_id])='Table'[machine_id]),'Table'[mission_start_time],,ASC)

2. Create a measure to calculate the difference :

Diff_1 = 
var current_ =MAX('Table'[Rank])
var last_ = CALCULATE(MAX('Table'[mission_end_time]),FILTER(ALL('Table'),'Table'[Rank]=current_-1))
return DATEDIFF(last_,MAX('Table'[mission_start_time]),MINUTE)

3. Calculate the the cycle time:

Diff_2 = 
var current_ =MAX('Table'[Rank])
var last_ = CALCULATE(MAX('Table'[mission_start_time]),FILTER(ALL('Table'),'Table'[Rank]=current_-1))
return IF([Diff_1]<3&&MAX('Table'[Rank])>1,MAX('Table'[mission_end_time])-last_,BLANK())

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

Hello thank you very much for the answer. Attached screen as it is:

Consultations:

1.-Although the cycle 282181-282183 is identified the following cycle is not identified (282183-282185)? They are recursive cycles.

2.-When applying the query I have filtered only 3 records in the table. What about the other records?

3.-In addition, there were other columns in the table (they are no longer there) that I did not include at the beginning, but that I did occupy them for other measures and now they do not work. Is the complete table needed?

Best regards

Hi Syndicate_Admin & Anonymous 
The following solution is based on my understanding of the problem. My understanding is that first the complete cycle need to be identified, then the duration need to be aggregated based on the complete cycle. This requires having a cycle number column which is the complex part of the solution, the rest is simple.
Also, I assumed that the 5% is with respect to the total duration between the two consequent points (stages).

Further, I assumed that this calculation shall take place for each individual machine. Apparently different days will automatically have different cycle numbers. 

Same Cycle as Previous = 
VAR CurrentPointFromID = 'Table'[point_from_id]
VAR CurrentPointToID = 'Table'[point_to_id]
VAR CurrentStart = 'Table'[mission_start_time]
VAR CurrentEnd = 'Table'[mission_end_time]
VAR CurrentMachineTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[machine_id] ) )
VAR TableBefore = FILTER ( CurrentMachineTable, 'Table'[mission_start_time] < CurrentStart )
VAR PreviousRecord = TOPN ( 1, TableBefore, 'Table'[mission_start_time] )
VAR PreviousPointToID = MAXX ( PreviousRecord, 'Table'[point_to_id] )
VAR PreviousStart = MAXX ( PreviousRecord, 'Table'[mission_start_time] )
VAR PreviousEnd = MAXX ( PreviousRecord, 'Table'[mission_end_time] )
RETURN
    IF ( 
        CurrentPointFromID = PreviousPointToID,
        VAR CycleLength = ABS ( DATEDIFF ( PreviousStart, CurrentEnd, SECOND ) )
        VAR CycleGap = ABS ( DATEDIFF ( PreviousEnd, CurrentStart, SECOND ) )
        RETURN
            IF ( CycleGap/CycleLength < 0.05, "Yes" )
    )

Cycle Number = 
VAR CurrentStart = 'Table'[mission_start_time]
VAR CurrentMachineTable = 
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[machine_id] ) )
VAR BlankTable = 
    FILTER ( CurrentMachineTable, 'Table'[Same Cycle as Previous] = BLANK ( ) )
VAR PreviousBlankTable =
    FILTER ( BlankTable, 'Table'[mission_start_time] <= CurrentStart )
RETURN
    COUNTROWS ( PreviousBlankTable )

Total Cycle Time = 
VAR TotalSeconds = 
    SUMX ( 
        VALUES ( 'Table'[Cycle Number] ),
        CALCULATE (
            VAR FirstStart = MIN ( 'Table'[mission_start_time] )
            VAR LastEnd = MAX ( 'Table'[mission_end_time] )
            RETURN
                DATEDIFF ( FirstStart, LastEnd, SECOND )
        )
    )
VAR Seconds = FORMAT ( MOD ( TotalSeconds, 60 ), "00" )
VAR TotalMinutes = QUOTIENT ( TotalSeconds, 60 )
VAR Minutes = FORMAT ( MOD ( TotalMinutes, 60 ), "00" )
VAR Hours = FORMAT ( QUOTIENT ( TotalMinutes, 60 ), "00" )
RETURN
    Hours & ":" & Minutes & ":" & Seconds