Calculating time between separate rows based on another column

thehalfboy , Create a column and following measure

New column
Approve Rank =
RANKX (
FILTER (
'Table',
'Table'[Requisition Number] = EARLIER ( 'Table'[Requisition Number] )
&& 'Table'[Action Code] = "APPROVE"
),
'Table'[Action Date],
,
DESC,
Dense
)

New Measure

Final Submit Date =
CALCULATE (
MAX ( 'Table'[Action Date] ),
FILTER (
allselected('Table'),
'Table'[Requisition Number] = max ( 'Table'[Requisition Number] )
&& 'Table'[Action Code] = "SUBMIT"
)
)

Final Approve Date =
CALCULATE (
MAX ( 'Table'[Action Date] ),
FILTER (
allselected('Table'),
'Table'[Requisition Number] = max ( 'Table'[Requisition Number] )
&& 'Table'[Action Code] = "APPROVE"
)
)

Time Between Submit and Approve (Hours) =
DATEDIFF (
[Final Submit Date],
[Final Approve Date],
HOUR
)

Penultimate Approve Date =
CALCULATE (
MAX ( 'Table'[Action Date] ),
FILTER (
allselected('Table'),
'Table'[Requisition Number] = max ( 'Table'[Requisition Number] )
&& 'Table'[Approve Rank] = 2
)
)

DATEDIFF (
[Penultimate Approve Date],
[Final Approve Date],
HOUR
)

For the first rank measure, you can use the new rank function to create measure

)


Penultimate Approve Date =
CALCULATE (
MAX ( 'Table'[Action Date] ),
FILTER (
'Table',
'Table'[Requisition Number] = EARLIER ( 'Table'[Requisition Number] )
&& 'Table'[Approve Rank] = 2
)
)

Hi thehalfboy ,

Please follow these steps:

1.        add new column

the final "SUBMIT" and the final "APPROVE" = 
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[Action Date] ),
        FILTER ( 'Table', 'Table'[Action Code] = "APPROVE" )
    )
VAR _2 =
    CALCULATE (
        MAX ( 'Table'[Action Date] ),
        FILTER ( 'Table', 'Table'[Action Code] = "SUBMIT" )
    )
RETURN
    DATEDIFF ( _2, _1, HOUR )

2.        sort APPROVE

the penultimate "APPROVE" and the final "APPROVE" =
RANKX (
    FILTER ( 'Table', 'Table'[Action Code] = "APPROVE" ),
    'Table'[Action Date],
    ,
    DESC
)

3.         the penultimate "APPROVE" and the final "APPROVE"

the penultimate "APPROVE" and the final "APPROVE" = 
   var _actDate = CALCULATE(
       MAX('Table'[Action Date]),
       FILTER('Table','Table'[sort] = 1)
   )
   var _actDate2 = CALCULATE(
       MAX('Table'[Action Date]),
       FILTER('Table','Table'[sort] = 2)
   )
  RETURN
     DATEDIFF(_actDate2,_actDate,MINUTE
         )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

Thank you for the replies. I'm sorry, but I think in my urge to simplify my data, I took it too far, and so the responses aren't working right. There are actually multiple different Requisition numbers, and so the calculation needs to find the "last submit" to "last approval" and "penultimate approval" to "last approval" for each requisition number in the table. Hopefully the data below is more like what is needed.