Days in a row working

[juancosta2911]

Hello,

I have a data set with people and days worked:

Person      date                              What I need

A              01/01/2022                     1

A              02/01/2022                     2

B             02/01/2022                      1
A              03/01/2022                     3
B              04/01/2022                     1

So is like each time is not the next day he should back to 1 so i can keep the record about the max days worked in a row.

I am open to solutions in M or DAX 

Thanks in advacnce

[v-easonf-msft · Accepted solution]

Hi， @juancosta2911 

Please try calculated columns as below:

Index = RANKX(filter('Table 1','Table 1'[Person]=EARLIER('Table 1'[Person])),'Table 1'[Date],,ASC,Dense) 

Group = 'Table 1'[Date]-'Table 1'[Index] 

New Index = RANKX(filter('Table 1','Table 1'[Person]=EARLIER('Table 1'[Person])&&'Table 1'[Group]=EARLIER('Table 1'[Group])),'Table 1'[Index],,ASC,Dense) 

Best Regards,
Community Support Team _ Eason