Forum Discussion

Sopall4424's avatar
Sopall4424
Regular Visitor
3 years ago

compare contact per agent

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 ...
Need Help
danextian's avatar
danextian
Icon for Super User rankSuper User
3 years ago

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's avatar
Sopall4424
Regular Visitor
3 years ago

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's avatar
    danextian
    Icon for Super User rankSuper User
    3 years ago

    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
            )
        )
    )