Forum Discussion
Calculate Response Time each time
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!
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.
5 Replies
- Greg_Deckler
Community Champion
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/339586
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-msftCommunity Support
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.
- 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
