Calculated column repeated calls

[Anonymous]

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 ID	Date of call	Column Needed: Repeated Customer
1	1-1-2021	No
1	8-1-2021	yes
1	7-2-2021	yes
2	1-1-2021	no
2	3-2-2021	no
2	5-2-2021	yes

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

[tamerj1]

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]

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 ID	Date of call	Repeated customer:	Min date:
1	1-1-2021	No	1-1-2021
1	8-1-2021	yes	1-1-2021
1	7-2-2021	yes	8-1-2021

You know how to do this?

Thanks in advance! 

[Anonymous]

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. 

[tamerj1 · Accepted solution]

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

[Anonymous]

This is it! Thank you very much!

[Anonymous]

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. 

[Tanushree_Kapse]

Hi @Anonymous ,

Not very clear with your question.

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

Thanks