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

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity 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. 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity 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" 
    )
)