compare contact per agent

[Sopall4424]

Hi 

here is my problem ; we create a KPI in our call center - For any agent answering call we want to do the following:

Agent A in a Month (let's call it month 1) answered let's say 10 call - from 1 to 10, identified by their phone number.

When come month 2, we want to see if any of these phone number from Month 1 called back again in Month 2.

Agent A in January || February

TFN1 : call in January|| did not call back in February = result = 0

TFN2  call in January || did call back || then result = 1

TFN3 call in January || did call back  || then result = 1

...

The final result would be that 2 out of 3 caller in Month 1 have called again in the following month

How can I do this with hundred of agents ? (dax or BI / Query is okay !)

Thank you for your time

Vincent

[Sopall4424]

Thank you danextian for you help!. My problem is not fully resolved but it was a big help for writting these lines of code for me.

v

[Sopall4424]

Thank you  for this response, I really appreciate it. Food for thought.
I know the hardest part is to clearly express your problem clearly. the solution is close to what I need but not exactly that.

Let's say agent A took 10 calls (call 1 .... call 10) in January. What I need to measure is not that in any of the call answered in January will call him back but just if any of the callers will call back again the next month ( I do not need the couple agent-call for the following month), I just want to check if the customer call back, not matter who he talked to, over the next 2 months after the first call.

Looking at your solution, I suppose that the solution is in the filter.. but not good enough to with dax to update your solution..

Vincent.

[danextian]

Hi @Sopall4424 ,

This is better off done in DAX as this involves scanning a table. Below is a sample formula and assumes that calculation is for the first and second months only - following months are to be ignored.

Second Month Caller by Agent = 
VAR _FirstDate =
    CALCULATE ( MIN ( Data[Date] ), ALLEXCEPT ( Data, Data[Agent], Data[Caller] ) ) // when was the customer first called the agent?
VAR _SecondDate =
    EDATE ( _FirstDate, 1 )
VAR _FirstPeriod =
    FORMAT ( _FirstDate, "YYYYMM" )
VAR _SecondPeriod =
    FORMAT ( _SecondDate, "YYYYMM" ) //when is the expected second month the customer should be calling again
RETURN
//return 1 only if it is the second month and the customer has called within that month
    IF (
        _SecondPeriod = Data[Period],
        CALCULATE (
            COUNTROWS ( Data ),
            FILTER (
                FILTER (
                    FILTER ( ALL ( Data ), Data[Agent] = EARLIER ( Data[Agent] ) ),
                    Data[Caller] = EARLIER ( Data[Caller] )
                ),
                Data[Period] = _SecondPeriod
            )
        )
    )

Please see attached pbix for your reference.

[danextian]

Hi @Sopall4424 ,

Try the following. Notice that I remove the parts that involved scanning the agent.

Second Month Caller = 
VAR _FirstDate =
    CALCULATE ( MIN ( Data[Date] ), ALLEXCEPT ( Data, Data[Caller] ) ) // when did the customer first called?
VAR _SecondDate =
    EDATE ( _FirstDate, 1 )
VAR _FirstPeriod =
    FORMAT ( _FirstDate, "YYYYMM" )
VAR _SecondPeriod =
    FORMAT ( _SecondDate, "YYYYMM" ) //when is the expected second month the customer should be calling again
RETURN
    //return 1 only if it is the second month and the customer has called within that month
    IF (
        _SecondPeriod = Data[Period],
        CALCULATE (
            COUNTROWS ( Data ),
            FILTER (
                FILTER ( ALL ( Data ), Data[Caller] = EARLIER ( Data[Caller] ) ),
                Data[Period] = _SecondPeriod
            )
        )
    )

[Sopall4424]

Thank you danextian ! This is really helpful -- I'm not exactly where I need to be but I do see that with DAX formula, I can achieve my goal. It seems like this thing comes easy for you !

Let me ask you this then :

Let's say an agent A anwer the following call in January

phone number 1

phone number 2

phone number 3

phone number 4

Month + 1 (February

I need to identify if any of the caller called back ( at the point the agent does not matter, have they called back if all that matter)

Let's say 

phone number 2

phone number 3

have called back

I would need to identify that these 2 calls back in February

Then in March, only phone number 3 call back.

I would need to identify that 1 call back in March

Thank you

vincent

[danextian]

You will need another column to identify whether a caller calls in both the second and third month.

Second and Third Month Caller = 
VAR _FirstDate =
    CALCULATE ( MIN ( Data[Date] ), ALLEXCEPT ( Data, Data[Caller] ) ) // when did the customer first called?
VAR _SecondDate =
    EDATE ( _FirstDate, 1 )
VAR _ThirdDate =
    EDATE ( _FirstDate, 2 )
VAR _FirstPeriod =
    FORMAT ( _FirstDate, "YYYYMM" )
VAR _SecondPeriod =
    FORMAT ( _SecondDate, "YYYYMM" ) //when is the expected second month the customer should be calling again
VAR _ThirdPeriod =
    FORMAT ( _ThirdDate, "YYYYMM" ) //when is the expected second month the customer should be calling again
VAR __SecondMonthCaller =
    CALCULATE (
        COUNTROWS ( Data ),
        ALLEXCEPT ( Data, Data[Caller] ),
        Data[Second Month Caller] = 1
    )
RETURN
    //return 1 only if it is the third month and the customer has called within that and the second month    
    IF (
        _ThirdPeriod = Data[Period], //YOU CAN CHANGE THIS TO "_ThirdPeriod = Data[Period] || _SecondPeriod = Data[Period] " IF  YOU WANT TO SHOW THE COUNT IN BOTH MONTHS
        CALCULATE (
            COUNTROWS ( Data ),
            FILTER (
                FILTER ( ALL ( Data ), Data[Caller] = EARLIER ( Data[Caller] ) ),
                Data[Period] = _SecondPeriod
            )
        )
    )