Getting time passed between two dates based on another field

[SebastianCostas]

First of all thanks a lot to whoever can help me.

The information I need to calculate is in a single table, and I need to get a column with this value for each row.

I have Purchase Requisitions with approvers and I need to know how much time each approver took.

The details I have are:

PR_ID: the purchase requisition ID

PR_Submited: The date and time the PR was submitted to approval (the same for each line for the same PR)

PR_Approver: who approved the PR

PR_Aprroved_Date: The date and time that approver approve that PR

Example:

PR_ID	PR_Submited	PR_Approver	PR_Approved_Date	Time Taken (needed)
1	01/01/2023 09:00 AM	Approver_1	01/01/2023 11:00 AM	2 hours
1	01/01/2023 09:00 AM	Approver_3	02/01/2023 09:00 AM	18 hours
1	01/01/2023 09:00 AM	Approver_2	01/01/2023 15:00 AM	4 hours

As you can see I need to compare the approved date to the previous date and it changes based on two fields and they are not in order:

If the Approver for a particular PR has the earliest approved date that would mean it's the first one and needs to calculate the time with the pr_submited, but if it's not the earlies it needs to calculate with the previous approved_date.

[SebastianCostas]

Thank you very much, it worked perfectly, I had to investigate how to create the index column but seeing the attached file was more than enough.

[v-jingzhang · Accepted solution]

Hi @SebastianCostas 

Please download my sample file to check my solution. 

I first add an Index column with Power Query, then use below DAX to calculate the spent hours in a calculated column.  

Time Taken = 
IF (
    'Table'[Index] = 1,
    DATEDIFF ( 'Table'[PR_Submited], 'Table'[PR_Approved_Date], HOUR ),
    DATEDIFF (
        CALCULATE (
            MAX ( 'Table'[PR_Approved_Date] ),
            ALLEXCEPT ( 'Table', 'Table'[PR_ID] ),
            'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
        ),
        'Table'[PR_Approved_Date],
        HOUR
    )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.