How do I work out which ID numbers are new, or repeats in a list each month

[StacyJEH]

I have the below data which shows which 'Person IDs' appear each month, I need to be able to replicate the last column in the table  - 'Frequency'.

'Frequency' needs to show if a 'Person ID' number is appearing for the first time ('New'), or was present the previous month ('Repeat') or if there has been a gap of one or more months that there have re-appeared but are not new ('Re-entry').

I can calculate the 'New' element by simply doing the duration between 'Involved Date' and 'First Date', if the result is zero labelling it as 'New' but the 'Repeat' and 'Re-entry' has me stumped?

Any help or suggestions would be welcomed.

Person ID	Involved Date	First date	Days between Involved and First	Frequency (Expected results)
1001	01/01/2024	01/01/2024	0	New
1001	01/03/2024	01/01/2024	60	Re-entry
1001	01/04/2024	01/01/2024	91	Repeat
1001	01/06/2024	01/01/2024	152	Re-entry
1002	01/02/2024	01/02/2024	0	New
1002	01/04/2024	01/02/2024	60	Re-entry
1002	01/05/2024	01/02/2024	90	Repeat
1003	01/03/2024	01/03/2024	0	New
1003	01/04/2024	01/03/2024	31	Repeat
1004	01/04/2024	01/04/2024	0	New
1005	01/01/2024	01/01/2024	0	New
1005	01/03/2024	01/01/2024	60	Re-entry
1005	01/05/2024	01/01/2024	121	Repeat
1006	01/02/2024	01/02/2024	0	New
1006	01/05/2024	01/02/2024	90	Re-entry
1007	01/02/2024	01/02/2024	0	New
1008	01/01/2024	01/01/2024	0	New
1008	01/02/2024	01/01/2024	31	Repeat
1009	01/02/2024	01/02/2024	0	New
1010	01/01/2024	01/01/2024	0	New
1010	01/03/2024	01/01/2024	60	Re-entry
1010	01/06/2024	02/01/2024	151	Re-entry

[AlienSx · Accepted solution]

let
    Source = your_table,
    fx = (tbl) => 
        [recs = Table.ToRecords(Table.Sort(tbl, "Involved Date")),
        gen = List.Generate(
            () => [i = 0, r = recs{0}, result = "New"],
            (x) => x[i] < List.Count(recs),
            (x) => 
                [
                    i = x[i] + 1, 
                    r = recs{i}, 
                    result = 
                        if Date.AddMonths(x[r][Involved Date], 1) = r[Involved Date]
                        then "Repeat"
                        else "Re-entry"
                ],
            (x) => x[r] & [result = x[result]]
        )][gen],
    group = Table.Group(
        Source, "Person ID",
        {"x", fx}
    ),
    result = Table.FromRecords(List.Combine(group[x]))
in
    result

[StacyJEH]

This works perfectly!!!!! Thank you so much.

This solution is being used in a report that will identify and allow protectection for vulnerable children so you've not only helped me but potentially you've helped thousands!! 

[ManuelBolz]

Hello @StacyJEH,

try the folowing Steps

1. You need a Copy of that query.
2. Name thay query "FrequencyLastInvolved"
3. Order FrequencyLastInvolved by [Involved Date] descending

4. Then use the function Table.Buffer()

5. Remove Dublicates on [Person ID]
6. Go to your first query "Frequency"
7. Leftjoin "FrequencyLastInvolved[Person ID]" to "Frequency[Person ID]"
8. Expand/Rename [Involved Date Last] from FrequencyLastInvolved
9. Now you have for each Row the latest [Involved Date] and you can your logic. 
if  [Involved Date Last] - [Involved Date Last] = 0 then "New"
else if  [Involved Date Last] - [Involved Date Last] >= 31 then "Re-entry"
else "Repeat"