If I understand correctly, based on your example table: when there is a value in the References column, it means that this row is the reply to the referenced MessageID.
The most difficult thing based on your example is the handling or the time strings. 28.10.2021 11.31.48 cannot be handled as date/time out of the box.
So, here is the DAX for a calculated column to calculate the response time, in minutes:
ResponseTime =
VAR Date1_String =
MINX(
CALCULATETABLE(
Table,
[Reference] = EARLIER([MessageId])
),
[DateTimeReceived]
)
VAR Date2_String = [DateTimeSent]
VAR Date1_Date =
FORMAT(
DATEVALUE(
SUBSTITUTE(
LEFT(
Date1_String,
FIND(" ", Date1_String)
),
".",
"/"
)
),
"dd/mm/yyyy"
) +
TIMEVALUE(
SUBSTITUTE(
RIGHT(
Date1_String,
FIND(" ", Date1_String)
),
".",
":"
)
)
VAR Date2_Date =
FORMAT(
DATEVALUE(
SUBSTITUTE(
LEFT(
Date2_String,
FIND(" ", Date2_String)
),
".",
"/"
)
),
"dd/mm/yyyy"
) +
TIMEVALUE(
SUBSTITUTE(
RIGHT(
Date2_String,
FIND(" ", Date2_String)
),
".",
":"
)
)
RETURN
IF(
NOT ISBLANK([References]),
DATEDIFF(
Date1_Date,
Date2_Date,
MINUTE
),
BLANK()
)
The code introduces a few variables :
Date1_String retrieves the DateTimeReceived from the initial message searching for the message with the MessageId in the current Reference column.Date2_String is the DateTimeSent from the current line. i.e. the reply.Date1_Date and Date2_Date is a (slightly convoluted) way to parse the string into a workable date/time format.
After the RETURN, a simple IF statement than calculate the time difference in minutes between the two date if only the column References is not empty.
