Calculate Response Time each time

[Arroios2020]

Hi
I am having trouble to calculate the response time of each message.

I have ID_Conversation for each conversation and a conversation can have multiple messagas (Content column). The column is_by_source tell me if the message was published by me or the client. What I want to know is how many hours and minutes I took to answer each message of client (is_by_source=False) and also how many time it took the customer to answer. Here it is the table:

	A	B	C	D	E	F
1	Id_Conversations	Created_DateTime	Content_Type	Is_By_Source	Content	Desired
2	5ee9cee62e583f6432901f73	17/06/2020 08:05	message	FALSE	text
3	5ee9cee62e583f6432901f73	17/06/2020 09:08	message	TRUE	text1	01:02:04 (B3-B2)
4	5eeacde9458a68a0d944eaf9	18/06/2020 02:13	post	FALSE	text2
5	5eeacde9458a68a0d944eaf9	18/06/2020 08:34	comment	TRUE	text3	06:20:23 (B5-B4
6	5eeacde9458a68a0d944eaf9	18/06/2020 09:22	message	FALSE	text4	00:47:41 (B6-B5)
7	5eeacde9458a68a0d944eaf9	18/06/2020 09:47	message	TRUE	text5	00:25:42 (B7-B6)

Can you help me?

Thanks!

[v-juanli-msft]

Hi @Arroios2020 

Great blog from Greg_Deckler, hope that provides ideas to slove your problem.

If not, please feel free to ask here.

Best Regards

Maggie

[Greg_Deckler]

@Arroios2020 - 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...

[Arroios2020]

Hi @Greg_Deckler 

Great article! I tried to use your formula but still I am having osme problems:

---

Last_Reply =

VAR next = MINX(FILTER(Table1,

Table1[Id_Conversations]=EARLIER(Table1[Id_Conversations]) &&

Table1[Created_DateTime]>EARLIER(Table1[Created_DateTime]) &&

Table1[Is_By_Source]<>"True"

),Table1[Created_DateTime])

RETURN IF(Table1[Is_By_Source]="False", 0,IF(ISBLANK(next),

DATEDIFF(Table1[Created_Date],NOW(),HOUR),

DATEDIFF(Table1[Created_Date],next,HOUR)

)

)

The results does not look ideal:

Id_Conversations	Id_Content	Created_DateTime	Last_Reply	Content_Type	Is_By_Source	Content
5eeacde9458a68a0d944eaf9	5eeacde80314c8640ea3ae3e	18/06/2020 02:13	0	post	FALSE	xxx
5eeacde9458a68a0d944eaf9	5eeb270e1326c983451a0b29	18/06/2020 08:34	18	comment	TRUE	xxxxx
5eeacde9458a68a0d944eaf9	5eeb32398c610c9a94ed752a	18/06/2020 09:22	0	message	FALSE	xxxxxxx
5eeacde9458a68a0d944eaf9	5eeb383e07f3f3d26e268712	18/06/2020 09:47	1024	message	TRUE	xxxx

[v-juanli-msft · Accepted solution]

Hi @Arroios2020 

Column =
RANKX (
    FILTER (
        'Table',
        'Table'[Id_Conversations] = EARLIER ( 'Table'[Id_Conversations] )
    ),
    [Created_DateTime],
    ,
    ASC,
    DENSE
)


Column 2 =
CALCULATE (
    MAX ( 'Table'[Created_DateTime] ),
    FILTER (
        'Table',
        'Table'[Id_Conversations] = EARLIER ( 'Table'[Id_Conversations] )
            && 'Table'[Column]
                = EARLIER ( 'Table'[Column] ) - 1
    )
)


Column 3 =
VAR ts =
    IF (
        [Column 2] <> BLANK (),
        DATEDIFF ( [Column 2], [Created_DateTime], SECOND )
    )
VAR h =
    INT ( ts / 3600 )
VAR d =
    INT ( h / 24 )
VAR m =
    INT ( MOD ( ts, 3600 ) / 60 )
VAR s = ts - h * 3600 - m * 60
VAR hh =
    IF ( LEN ( h ) = 1, 0 & h, h )
VAR mm =
    IF ( LEN ( m ) = 1, 0 & m, m )
VAR ss =
    IF ( LEN ( s ) = 1, 0 & s, s )
RETURN
    IF (
        [Column 2] <> BLANK (),
        IF (
            d = BLANK (),
            hh & ":" & mm & ":" & ss,
            d & " " & hh & ":" & mm & ":" & ss
        )
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SaraID]

This worked great! But, I need help now converting that column from text to time. Thanks!