identify missed calls

[Lodewyk]

hi there,

the requirement is that need to identify which missed calls were returned within 3 days.

in the below, miss caller 1 call was returned on the 1st. so it has to count as a returned called.

miss caller 2 was returned after the 3 days SLA, so that will count as call not returned

Direction	To	From	Date/Time	Result
inbound	receiver 1	miss caller 1	8/30/2024	missed
inbound	receiver 1	miss caller 3	8/30/2024	missed
inbound	receiver 2	miss caller 3	9/1/2024	missed
inbound	receiver 2	miss caller 2	9/1/2024	missed
inbound	miss caller 1	receiver 2	9/1/2024	answered
inbound	miss caller 1	receiver 1	9/1/2024	answered
inbound	miss caller 2	receiver 1	9/6/2024	answered
inbound	miss caller 3	receiver 2	9/6/2024	answered

[PavanLalwani]

To identify which missed calls were returned within 3 days in Power BI or using DAX, we need to create a logic that matches a missed call with a corresponding answered call from the same caller within 3 days.

Here’s a step-by-step solution:

### 1. Create a Relationship Between Missed and Answered Calls
You need to compare the "missed" and "answered" calls for each `miss caller`. The calls are linked by the `From` and `To` columns, and we will check the date difference between the missed and returned calls.

### 2. Add a Calculated Column to Identify Returned Calls

We can use a calculated column to check if the missed call was returned within 3 days by matching the caller's missed and answered calls:

```DAX
ReturnedWithin3Days =
VAR MissedDate = 'Calls'[Date/Time]
VAR Caller = 'Calls'[From]
VAR ReturnedCall =
CALCULATE(
MIN('Calls'[Date/Time]),
FILTER(
'Calls',
'Calls'[From] = Caller &&
'Calls'[Result] = "answered" &&
'Calls'[Date/Time] > MissedDate &&
'Calls'[Date/Time] <= MissedDate + 3
)
)
RETURN IF(NOT(ISBLANK(ReturnedCall)), "Returned", "Not Returned")
```

### Explanation:
1. **MissedDate**: This stores the date of the missed call.
2. **Caller**: This stores the caller's ID (`From` field) of the missed call.
3. **ReturnedCall**: This calculates the minimum date of an "answered" call that is greater than the missed date and less than or equal to 3 days after the missed call.
4. **IF condition**: If the `ReturnedCall` is not blank (i.e., a matching answered call exists within 3 days), it marks the call as "Returned"; otherwise, it is marked as "Not Returned."

### 3. Handling Multiple Missed Calls from the Same Caller
If the same caller makes multiple missed calls, the formula will check each missed call independently to see if there was a return call within 3 days of that specific missed call.

### 4. Create a Report Based on Returned and Not Returned Calls
Once the column is created, you can now visualize or filter the data based on whether the missed calls were returned or not. You can create:
- A **table or matrix** to list all calls and their status (returned/not returned).
- **KPIs** or **bar charts** to count how many calls were returned within 3 days vs. those not returned.

This method allows you to easily identify and count missed calls that were followed up on within the 3-day SLA. Let me know if you need further customization or explanations!

[Anonymous]

Thanks for dharmendars007's concern about this issue.

Hi, @Lodewyk 

I am glad to help you.

Maybe you can create a calculation table for my DAX that you can refer to:

ReturnedStatus = 
FILTER(
    ADDCOLUMNS(
        SUMMARIZE('Table1', 'Table1'[From]),
        "ReturnedWithin3Days", 
        VAR CurrentCaller = 'Table1'[From]
        VAR MissedCallDateTime = 
            CALCULATE(
                MIN('Table1'[Date/Time]),
                FILTER('Table1', 'Table1'[From] = CurrentCaller && 'Table1'[Result] = "missed")
            )
        VAR AnsweredCallDateTime = 
            CALCULATE(
                MIN('Table1'[Date/Time]),
                FILTER('Table1', 'Table1'[To] = CurrentCaller && 'Table1'[Result] = "Answered" && 'Table1'[Date/Time] > MissedCallDateTime)
            )
        VAR TimeDifference = DATEDIFF(MissedCallDateTime, AnsweredCallDateTime, DAY)
        RETURN IF(NOT(ISBLANK(AnsweredCallDateTime)) && TimeDifference < 3, "Returned", "Not Returned")
    ),
    'Table1'[From] IN {"miss caller 1", "miss caller 2", "miss caller 3"}
)

I have attached the pbix file for this example below, I hope it helps.

[Lodewyk]

@dharmendars007 , please let me know if it is possible not to harcode "

 'Table1'[From] IN {"miss caller 1", "miss caller 2", "miss caller 3"}

Because "miss caller 1" represents a telephone number and there will be hundreds of telephone numbers in to the "From" column

[Anonymous · Accepted solution]

Hi, @Lodewyk 

Thanks your reply.

Please refer to the new DAX formula:

ReturnedStatus2 = 
SUMMARIZE (
    ADDCOLUMNS (
        FILTER (
            GENERATE (
                SELECTCOLUMNS (
                    FILTER ( 'Table1', CONTAINSSTRING ( 'Table1'[From], "miss caller" ) ),
                    "_To1", 'Table1'[To],
                    "_From1", 'Table1'[From],
                    "_result1", 'Table1'[Result],
                    "_Date1", 'Table1'[Date/Time]
                ),
                SELECTCOLUMNS (
                    FILTER ( 'Table1', CONTAINSSTRING ( 'Table1'[From], "receiver" ) ),
                    "_To2", 'Table1'[To],
                    "_From2", 'Table1'[From],
                    "_Date2", 'Table1'[Date/Time],
                    "_result2", 'Table1'[Result]
                )
            ),
            [_From1] = [_To2]
        ),
        "ReturnedWithin3Days",
            IF (
                DATEDIFF ( [_Date1], [_Date2], DAY ) <= 3
                    && [_result2] = "answered",
                "Returned",
                "No Returned"
            )
    ),
    [_From1],
    [ReturnedWithin3Days]
)

I have updated the new pbix file below, hope this helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lodewyk]

Thank you, will revert as soon as i can

[Lodewyk]

in the above example. only miss caller 1 was returned in the 3 days

[Lodewyk]

when a call is missed the "From" column will have the telephone number, with the date when the call came in. If the telephone number is in the "To" column, within the 3 days then the call was received in the SLA.

[dharmendars007]

Hello @Lodewyk , 

You can create the measure to count the number of missed calls returned within 3 days like the below

CountReturnedCallsWithin3Days =
CALCULATE(
COUNTROWS('Calls'),
FILTER(
'Calls',
'Calls'[Result] = "missed" &&
'Calls'[ReturnedWithin3Days] = "Returned"))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Lodewyk]

Thanks for the advise. Question, where do you define the date difference between when the miss call came in and when it was returned?