Frequent callers to call center

[Anonymous]

Hi 

I have data where it has phone numbers, callID, Date like below, I would like to add new column if phone numbers are same and call Id is different and date is similar I would like to tag as called on same day , if phone number is same and call ID is different and date is diffent i would like to tag as per teh date difference called after 1day or 2days, if the phone numbers are same and call ID is same I dnt want to tag them.

phone number	CallID	Date	New Column
123-456-789	ABC	15-sep-2022	Called on same day
123-456-789	ABC	15-sep-2022
123-456-789	XYZ	15-sep-2022	Called on same day
123-456-789	GHC	16-sep-2022	Called on next day
789-123-456	JKU	18-sep-2022
789-123-456	YOU	20-sep-2022	called after 2days
456-789-123	HBJ	20-sep-2022	non repeat
456-789-123	HBJ	20-sep-2022	non repeat

           Please help me on this
       

[Anonymous]

Thank you so much! It worked 🙂

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You need to sort the table and add an index column in PQ like:

Then  try calculated column like:

Index1 = RANKX(FILTER(ALL('Table'),'Table'[phone number]=EARLIER('Table'[phone number])),'Table'[Index],,ASC,Dense)

Result =
VAR _mindate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[phone number] ), 'Table'[Index1] = 1 )
    )
VAR _datediff =
    DATEDIFF ( _mindate, 'Table'[Date], DAY )
RETURN
    SWITCH (
        TRUE (),
        'Table'[Index1] = 1, BLANK (),
        _datediff = 0, "Called on same day",
        _datediff = 1, "Called on next day",
        "Called after " & _datediff & " days"
    )

Best Regards,
Community Support Team _ Eason