Flag multiple calls within a number of consecutive days

[GKJARC]

Hi All,

The aim is to flag when a customer calls by telephone three times or more within 5 days.
Here's an example of the desired outcome, the first 4 columns are already in the dataset:

The rows for each customer are already grouped with an index number.
I created a simplified PBIX-file, however since I'm not a super user it seems not possible to upload it here. The data model is this:

Hopefully this is sufficient info, otherwise please let me know so I can clarify.

Thanks!

[v-jianboli-msft · Accepted solution]

Hi @GKJARC ,

According to your description, it seems that the values of flag(Index 1 , 2 , 3) should be 1. If I misunderstood, please clearify the real logic.

Or if your logic is flag when a customer calls by telephone three times or more within 5 days, please try:

Flag = 
VAR _a =
    COUNTROWS (
        FILTER (
            'Table',
            [Customer name] = EARLIER ( 'Table'[Customer name] )
                && [Call date]
                    >= EARLIER ( 'Table'[Call date] ) - 5
                && [Call date]
                    <= EARLIER ( 'Table'[Call date] ) + 5
        )
    )
RETURN
    IF ( _a >= 3, 1, 0 )

Final output:

[GKJARC]

You're right, the first 3 rows in the example data should also be flagged.
Thanks for your solution!

[GKJARC]

Table in text format:

Index	Customer name	Call date	DateInteger
1	Joe	08-05-2022 08:25:00	20220508
2	Joe	08-05-2022 10:30:00	20220508
3	Joe	08-05-2022 13:00:00	20220508
4	Joe	16-05-2022 14:30:00	20220516
5	Joe	16-05-2022 15:00:00	20220516
6	Joe	17-05-2022 14:30:00	20220517
7	Joe	18-05-2022 15:00:00	20220518
8	Joe	20-05-2022 15:20:00	20220520
9	Joe	02-08-2022 10:12:00	20220802
10	Joe	10-08-2022 10:12:00	20220810
11	Joe	17-08-2022 10:12:00	20220817
12	Joe	19-08-2022 10:12:00	20220819
1	Peter	04-05-2022 09:05:00	20220504
2	Peter	20-05-2022 11:00:00	20220520
3	Peter	22-05-2022 09:00:00	20220522
4	Peter	23-05-2022 09:05:00	20220523
5	Peter	06-09-2022 09:05:00	20220906
6	Peter	06-09-2022 16:00:00	20220906