Find delay to first response in extracted teams channel chats.

[Borioki]

Hey everyone, thanks for taking a look, I've learnt a lot from this forum and this is my first posted question.

I have a table of chat history I'll include an example of, I'm trying to find the average delay for first response to each original message. However the way the data is structured is stumping me on coming up with a calculated field for this. 

timestamp	message_body	message_id	original_message_id
5/8/24 12:27:30 PM	Try asking on the forum	123456789	11123456
5/8/24 12:27:21 PM	I'm not sure	234567891	11123456
5/8/24 12:21:19 PM	How do I find delay to response?	11123456	[blank]

I'm stumped in part cause of how the [original_message_id] field is blank for the actual original messages, I figure then it would at least be easier to find the time difference between the first and second row, as sorted by timestamp, for each unique [original_message_id].

Appreciate any assistance!

[Greg_Deckler · Accepted solution]

@Borioki 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 ) * 1.