Calculated Column required to evaluate maximum number of consecutive events attended

[noodles281]

I'm going around in circles with this now. So posting on here to see if a fresh pair of eyes will solve it. Thank you so much for your attention and participation.

Events take place, and people attend these. I have attended each event and would like to know how many consecutive events any participant has had and who is on the longest current streak (so involving the most recent event). All my thoughts suggest this should be a calculated column either in the EVENTS table or the PARTICIPANTS table. Still, I can't work out a way to increment the count, so the consecutive attendances are counted properly.

In my mind, the result should be in one of these two formats.

EVENTS

EventNumber	PersonID	ConsecutiveCount
1	66	1
2	66	2
3	66	3
5	66
6	66
7	66
8	66	1
10	66	2
12	66
13	66
14	66	1
16	66	2
17	66	3
20	66
21	66
1	48	1
2	48
3	48	1
4	48
7	48
8	48	1
9	48	2
10	48	3
11	48	4
12	48	5
14	48
15	48
16	48
17	48

or

PARTICIPANTS

PersonID	Consecutive Count
66	3
48	5

Download the EVENTS table ( contains EventNumbers and PersonID)

https://geordieintelligence-my.sharepoint.com/:f:/g/personal/ross_geordieconsulting_co_uk/EiPMJAmWVP... 

[Jihwan_Kim · Accepted solution]

Link to the PBIX file 

[noodles281]

Thank you, a couple of tweaks and it's now in as a calculated column so I can more easily use it for the metrics, people want to quickly see who's currently holding the consecutive attendance record etc... Massive help and glad to see I wasn't a too far off either 👍