Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated column repeated calls

Hi! 

 

I'm looking to make an column in PowerBi which measured if a call is a repeated call or not within 1 month of the last month. 

 

The data looks like this:

 

Customer IDDate of callColumn Needed: Repeated Customer
11-1-2021No
18-1-2021yes
17-2-2021yes
21-1-2021no
23-2-2021no
25-2-2021yes

 

I was thinking about an IF statement but can't really figure out how to do the dates part. 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 

     

    First Date = 
VAR NearestDate =
    MAXX ( 
        FILTER ( 
            'Table', 
            'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] )
                && 'Table'[Date of call] < EARLIER ( 'Table'[Date of call] )
        ),
        'Table'[Date of call]
    )
VAR Result =
    IF (
        ISBLANK ( NearestDate ),
        'Table'[Date of call],
        NearestDate
    )
RETURN
    Result
    Needed: Repeated Customer = 
IF ( 
    'Table'[First Date] = 'Table'[Date of call], 
    "No",
    IF ( 
        DATEDIFF ( 'Table'[First Date], 'Table'[Date of call], DAY ) >= 31, 
        "No", 
        "Yes" 
    )
)

     

7 Replies

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Impactful Individual
    4 years ago

    Hi Anonymous ,

    Not very clear with your question.

    Can you please provide the sample result or the expected result here.

     

    Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    The expected result is an calculation based on the first two columns as seen in column C. So if their is an call from the same ID within one month before than YES else NO. 

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    You are right. It is a two step procedure:
    1. Retreieve the first date in a new calculated column 

    First Date =
MINX (
    FILTER ( 'Table', 'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] ) ),
    'Table'[Date of call]
)

    2. Use the IF in another new calculated column:

    Needed: Repeated Customer =
IF ( 'Table'[First Date] = 'Table'[Date of call], "No", "Yes" )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Almost there! I need one more step. It is possible that there is more than 2 calls per call id. In this case i don't want to take the min date but the closest min date.

       

      So for example:

      Customer IDDate of callRepeated customer:Min date:
      11-1-2021No1-1-2021
      18-1-2021yes1-1-2021
      17-2-2021yes8-1-2021

       

      You know how to do this?

       

      Thanks in advance! 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        As showed in the example. I need to calculate if an call is within the delta of 31 days (one month) because if its greater than 31 days it is not measured as an repeated call. This is not possible when the min date is always the earliest date this is not possible with customers who called more than 2 times.