Need Help with hour calculation between state

[davehardikkumar]

Hello Experts, I have below table.

id       teamname           from                                             to                                           state

1          Red                    04/23/2023    7:15:21 PM     04/23/2023  7:15:25    PM             Pending

1          Red                    04/23/2023    7:15:25 PM     04/23/2023  7:16:01    PM             Pending

1          Red                    04/23/2023    7:16:18 PM     04/23/2023  11:59:59  PM             Pending

1          Red                    04/23/2023    7:16:01 PM     04/23/2023  7:16:18    PM            In Progress

1          Red                    04/23/2023    7:16:18 PM     04/23/2023  7:16:25    PM            In Progress

2          Blue                   03/23/2023    3:15:23 PM     03/23/2023  3:15:25    PM             Pending

2          Blue                   03/23/2023    3:15:26 PM     03/23/2023  3:16:01    PM             Pending

2          Blue                   03/23/2023    3:16:19 PM     03/23/2023  3:59:55    PM             Pending

2          Blue                   03/23/2023    3:16:03 PM     03/23/2023  3:16:22    PM            In Progress

2          Blue                   03/23/2023    3:16:23 PM     03/23/2023  3:16:29    PM            In Progress

Need your help! I have date parameter from and to and I want to display pick time for each ID. The pick up time should count the difference from first in pending time minus first in progress state time for each team. how can I achieve that?

[Greg_Deckler]

@davehardikkumar Strikes me as a variation of MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous