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

     

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This is it! Thank you very much!