identify missed calls

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 

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.

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

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.

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!