Forum Discussion

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 ID Date of call ...

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

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

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

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.

tamerj1

Community Champion

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

Anonymous
Not applicable
4 years ago
This is it! Thank you very much!