Count based on Timeperiod

[sabeensp]

Hello,

Here is my dataset. What I'm trying to do is this.

If the caller and Call Type is same and Date is not within 5 days then count that as "First Attempt Call Resolved"
If the caller and Call Type is same and Date is not with the 5 days then count that as "Not resolve din first attempt"

John = 2 "calls were clodes in first attempt" because Caller is the same, Call type is the same, but date for both records are outside of 5 days

Jerry = Should not be counted, becaus ehe called back for the same (both record dates are within 5 days)

Sam = 2 count because Caller is same, but Call type is different, even date is within 5 days

Excel can be downloaded from here

https://1drv.ms/x/s!AumyvtVXEve9jLxV0sdgE0BPIRbu8g

Thanks.

[v-yuta-msft]

Hi sabeensp,

"If the caller and Call Type is same and Date is not within 5 days then count that as "First Attempt Call Resolved" If the caller and Call Type is same and Date is not with the 5 days then count that as "Not resolve din first attempt""

<--- No so clear about your requirement such as "" count that as "Not resolve din first attempt" "", a sample data/table containing expected results will be much appreciated.

Regards,

Jimmy Tao

[sabeensp]

@v-yuta-msft

Total Call Resolved first attemp = 5, because of rows in yellow. Date between rows of same caller and call type are > then  5 days.

If Caller and Call Type are teh same and dates are within the 5 days then it means call was not resolved in first attempt (Records in White)

[v-yuta-msft · Accepted solution]

Hi sabeensp,

Sorry for late, to achieve your requirement, you can create a measure using DAX like below:

Result = 
VAR interval = CALCULATE(DATEDIFF(MIN(Table1[Date]), MAX(Table1[Date]), DAY), ALLEXCEPT(Table1, Table1[Call Type], Table1[Caller]))
VAR rows_number = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Caller]))
VAR call_type_number = CALCULATE(DISTINCTCOUNT(Table1[Call Type]), ALLEXCEPT(Table1, Table1[Caller]))
RETURN
IF(rows_number <> 1, IF(interval > 5, rows_number, IF(call_type_number = rows_number, rows_number, "Not resolve din first attempt")))

 

Regards,

Jimmy Tao