The max consecutive work days

[babyjb1979]

Hi,

I am asked to create a calculation of employee consecutive work days. For specific period, want to see every employee's max consecutive work days. I am trying to write a measure, but stuck. Please help! 

Consecutive Work Days =

var tb0=

ADDCOLUMNS(

SUMMARIZE('Employee Transaction','Employee Transaction'[Employee Number],'Employee Transaction'[Work Date]),"Consecutive Days",

IF(ISINSCOPE('Employee Transaction'[Employee Number]),

var tb1=CALENDAR(FIRSTDATE('Date'[Date]),LASTDATE('Date'[Date]))

var tb2=EXCEPT(tb1,ALL('Employee Transaction'[Work Date]))

var tb3=MAX('Date'[Date])

var tb4=MAXX(FILTER(tb2,[Date]<=tb3),[Date])

Return

INT(tb3-tb4)))

Return

tb0

Employee ID	Work Date
A	1/1/2023
A	1/2/2023
A	1/3/2023
B	1/7/2023
A	1/5/2023
A	1/6/2023
A	1/7/2023
A	1/8/2023
B	1/10/2023
A	1/9/2023
A	1/10/2023
A	1/20/2023
B	1/22/2023
A	1/21/2023
A	1/22/2023
A	1/23/2023
B	1/23/2023
B	1/24/2023
A	1/24/2023
A	1/25/2023
A	1/26/2023
A	1/27/2023
B	1/28/2023
B	1/29/2023
B	1/30/2023
B	1/31/2023
B	2/1/2023
B	2/2/2023

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[Greg_Deckler]

@babyjb1979 This might help, it was created for something similar. Cthulhu - Microsoft Fabric Community

[babyjb1979]

But your method is when the index column is ready. My situation is no new column can be added into the dataset. Is there a way to resolve just by DAX?