Calculate time spent in process excluding stages

[m0v08wo]

Dear Power BI team,
I am a new person to the community and would like find the optimal way of calculating work logs.

I have a work log of ticket processing. I need to calculate total time spent per each ticket for 2 specific stages:
- Between "Submited" and "Process_Started".

- Between "Process_Started" and the very first result ("Rejected" or"Approved") but excluding "Process_Saved" in between.
Both statuses should appear for the very first time since the process is repetetative.
I would be very glad for any options. 
Thank you very much.

This is how it looks like:

This is the raw data:

log_id	ticket_id	Action_Start	Action_End	Action_Start_Time	Action_End_Time	Hours_Spent
18792	123123	Create	Checked	1/27/2021 4:12	1/27/2021 4:33	0.34
18793	123123	Checked	Checked	1/27/2021 4:33	1/27/2021 4:33	0.00
18794	123123	Checked	Submited	1/27/2021 4:33	1/27/2021 4:33	0.01
18795	123123	Submited	Assigned	1/27/2021 4:33	1/27/2021 13:53	9.34
18874	123123	Assigned	Process_Started	1/27/2021 13:53	1/27/2021 14:52	0.98
18924	123123	Process_Started	Process_Saved	1/27/2021 14:52	1/27/2021 15:05	0.22
18930	123123	Process_Saved	Rejected	1/27/2021 15:05	1/29/2021 16:52	49.78
19900	123123	Rejected	Reviewed	1/29/2021 16:52	1/29/2021 16:54	0.03
19901	123123	Reviewed	Reviewed	1/29/2021 16:54	1/29/2021 13:17	1.38
19942	123123	Reviewed	Checked	1/29/2021 13:17	1/29/2021 13:28	0.18
19955	123123	Checked	Checked	1/29/2021 13:28	1/29/2021 13:28	0.00
19956	123123	Checked	Submited	1/29/2021 13:28	1/29/2021 13:28	0.01
19959	123123	Submited	Assigned	1/29/2021 13:28	1/29/2021 13:41	0.22
19962	123123	Assigned	Process_Started	1/29/2021 13:41	1/29/2021 13:42	0.01
19966	123123	Process_Started	Viewed	1/29/2021 13:42	1/29/2021 13:47	0.09
19968	123123	Viewed	Rejected	1/29/2021 13:47	1/29/2021 13:48	0.02
19969	123123	Rejected	Checked	1/29/2021 13:48	1/29/2021 13:59	0.18
19978	123123	Checked	Checked	1/29/2021 13:59	1/29/2021 13:59	0.00
19979	123123	Checked	Submited	1/29/2021 13:59	1/29/2021 19:00	0.01
19980	123123	Submited	Viewed	1/29/2021 19:00	1/29/2021 19:19	0.33
19996	123123	Viewed	Assigned	1/29/2021 19:19	1/29/2021 19:20	0.01
19998	123123	Assigned	Process_Started	1/29/2021 19:20	1/29/2021 19:20	0.00
19999	123123	Process_Started	Completed	1/29/2021 19:20	1/29/2021 19:20	0.01
20000	123123	Completed	Released	1/29/2021 19:20	1/29/2021 19:20	0.00
20001	123123	Released	Viewed	1/29/2021 19:20	1/29/2021 21:48	2.46
20038	123123	Viewed	Viewed	1/29/2021 21:48	1/30/2021 13:33	15.75

[Anonymous · Accepted solution]

Hi @m0v08wo,

You can try to use the following calculate column formulas if they suitable for your requirement:

1st stages = 
IF (
    Table1[Action_Start] = "Submited",
    VAR endID =
        CALCULATE (
            MIN ( Table1[log_id] ),
            FILTER (
                Table1,
                [log_id] > EARLIER ( Table1[log_id] )
                    && Table1[Action_Start] = "Process_Started"
            )
        )
    RETURN
        CALCULATE (
            SUM ( Table1[Hours_Spent] ),
            FILTER ( Table1, [log_id] >= EARLIER ( Table1[log_id] ) && [log_id] <= endID )
        )
)

2nd stages = 
IF (
    Table1[Action_Start] = "Process_Started",
    VAR endID =
        CALCULATE (
            MIN ( Table1[log_id] ),
            FILTER (
                Table1,
                [log_id] > EARLIER ( Table1[log_id] )
                    && Table1[Action_Start] IN { "Rejected", "Approved" }
            )
        )
    RETURN
        CALCULATE (
            SUM ( Table1[Hours_Spent] ),
            FILTER (
                Table1,
                [log_id] >= EARLIER ( Table1[log_id] )
                    && [log_id] <= endID
                    && Table1[Action_Start] <> "Process_Saved"
            )
        )
)

Regards,

Xiaoxin Sheng