Get the Time Difference

[Leslie1015]

Hello everyone,

Can someone help me create a calculated column to get the ouput below?

ID	Date	Index	Output
A	8/3/2023 7:45:05	1
A	8/3/2023 7:45:15	2	0:00:10
A	8/3/2023 7:45:17	3	0:00:02
B	8/3/2023 7:45:21	1
B	8/3/2023 7:45:27	2	0:00:06
A	8/3/2023 7:45:27	4	0:00:10
A	8/3/2023 7:46:01	5	0:00:34
A	8/3/2023 7:46:15	6	0:00:14
C	8/3/2023 7:46:31	1
C	8/3/2023 7:46:50	2	0:00:19
C	8/3/2023 7:47:20	3	0:00:30
B	8/3/2023 7:47:40	3	0:02:13

[v-cgao-msft · Accepted solution]

Hi @Leslie1015 ,

Calculated column:

Output 2 = 
VAR CurrentID = TableName[ID]
VAR CurrentIndex = TableName[Index]
VAR CurrentDate = TableName[Date]
VAR PreviousRow = 
    CALCULATE (
        MAX ( TableName[Date] ),
        ALLEXCEPT ( TableName, TableName[ID] ),
        TableName[Index] = CurrentIndex - 1
    )
RETURN
IF (
    CurrentIndex > 1,
    CurrentDate - PreviousRow,
    BLANK ()
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Greg_Deckler]

@Leslie1015 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