Identify cycles in records

[Syndicate_Admin]

Hello everyone, along with greeting you I wanted to see if anyone within the group could give me a help to be able to solve this. This is an extract from an event log from a source-to-destination point. The topic I need to capture is when it is a cycle (i.e. when it returns to the same point of origin, same equipment). For example, 282181 and 282183 events are a cycle and it can be verified that the mission end date of the 282181 event is the beginning of the start date of the next event in the event 282183. This date can be exactly the same (case explained above), as there may be a delay and may not be exactly the same, so that for it to be fulfilled that it is a cycle the difference must not be greater than 5%. Then the same event 282183 and the event 282185 form another cycle and so on with the other records, fulfilling the conditions explained above of the same equipment and the same date, it is considered a cycle.

According to this my intention is to calculate the times for each cycle (end time - start time) and then with that cycle time to obtain other management indicators. Example for the 282181 cycle and 282183 the cycle time is 0:03:59.

I hope someone can help me. Thank you very much!

event_id	machine_id	point_to_id	point_from_id	mission_start_time	mission_end_time
282181	730	S0401N	PV04	11-02-2022 10:01:51	11-02-2022 10:03:58
282183	730	PV04	S0401N	11-02-2022 10:03:58	11-02-2022 10:05:50
282185	730	S0401N	PV04	11-02-2022 10:05:51	11-02-2022 10:08:18

[tamerj1]

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

[Syndicate_Admin]

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

[lbendlin]

Please provide sanitized sample data that fully covers your issue. 

Please show the expected outcome based on the sample data you provided. 

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Syndicate_Admin]

Hello thank you for your reply. It yields this result, which is not expected, in addition to not identifying the cycle. Best regards

[V-lianl-msft]

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.

[lbendlin]

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".