Identify missed and returned calls

[Kaysa]

Hi there

When a call comes in the and the call is missed , i would like to know if the call was returned.

When the Result is Missed, that identifies a "Missed Call". I would like to know if the call is returned in 3 days, then it must count as a returned call. The "From" number for the missed call will be in the "To" column, will be the same.

Direction	To	From	Date/Time	Result
inbound	(123) 123-1231	(011) 111-1111	8/30/2024	missed
inbound	(123) 123-1231	(333) 111-2222	8/30/2024	missed
inbound	(842) 842-8421	(786) 223-223	9/1/2024	missed
inbound	(899) 899-8999	(222) 222-2222	9/1/2024	missed
inbound	(011) 111-1111	(444) 444-4444	9/1/2024	answered
inbound	(011) 111-1111	(444) 444-4444	9/1/2024	answered
inbound	(222) 222-2222	(899) 899-8999	9/6/2024	answered

[Anonymous · Accepted solution]

Hi @Kaysa ,

Based on the testing, please try the following methods:

1.Create the sample table.

2.Create the calculated column to identify if a missed call was returned.

ReturnedCall = 
VAR ReturnedCalls = 
    FILTER(
        'Table',
        'Table'[Result] = "missed" &&
        'Table'[From] = EARLIER('Table'[To]) &&
        'Table'[Date] <= EARLIER('Table'[Date]) + 3
    )
RETURN
    IF('Table'[Result] = "answered", "answered",
        IF(COUNTROWS(RETURNEDCALLS) > 0, "Returned", "Not Returned")
    )

3.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kaysa]

Direction	To	From	Date/Time	Result
inbound	(123) 123-1231	(011) 111-1111	8/30/2024	missed
inbound	(123) 123-1231	(333) 111-2222	8/30/2024	missed
inbound	(842) 842-8421	(786) 223-223	9/1/2024	missed
inbound	(899) 899-8999	(222) 222-2222	9/1/2024	missed
inbound	(011) 111-1111	(444) 444-4444	9/1/2024	answered
inbound	(011) 111-1111	(444) 444-4444	9/1/2024	answered
inbound	(222) 222-2222	(899) 899-8999	9/6/2024	answered

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.

[Lodewyk]

@Ashish_Mathur , please see table as per your request

[Ashish_Mathur]

There is nothing i can understand there.  Someone who does will help you.

[lbendlin]

when multiple calls come in from the same number and are all missed, and then someone calls that number back - how do you know which of the missed calls they respond to?  They could also respond to a call that was missed last month?