Calculate consecutive days and reset the count each time there's a break

[WorkHard_]

Spent a few days attempting this and there are a lot of variations online based on how the databased is stored.
Trying to calculate consecutive days for a dataset that lists one row per day for each employee. The dataset will list a date regardless if they worked or not and I catch that using the WorkedFlag. (if Date NOT blank, 1,0)

Employee Name	Date	WorkedFlag	DesiredStreakGroup	DesiredStreak
John	8/1/2025	1	0	3
John	8/2/2025	1	0	3
John	8/3/2025	1	0	3
John	8/4/2025	0
John	8/5/2025	1	1	2
John	8/6/2025	1	1	2
John	8/7/2025	0
John	8/8/2025	1	2	1
John	8/9/2025	0
John	8/10/2025	1	3	1
John	8/11/2025	0
John	8/12/2025	0

I believe I need to solve this in 2 stages.
First stage is to identify a streak group that will increment each time a break in the consecutive days occurs.
So for each employee, calculate a unique incrementing streak group.
This is the best attempt right now which keeps incrementing the streak group on break days for some reason.

StreakGroup =
VAR Emp = MainTable[Employee Name]
VAR CurrentDate = MainTable[Date]
VAR RowNum =
CALCULATE(
    COUNTROWS(MainTable),
    FILTER(
        MainTable,
        MainTable[Employee Name] = Emp &&
        MainTable[Date] <=CurrentDate
    )
)
VAR WorkCount =
CALCULATE(
    COUNTROWS(MainTable),
    FILTER(
        MainTable,
        MainTable[Employee Name] = Emp &&
        MainTable[Date] <= CurrentDate &&
        MainTable[WorkedFlag] = 1
    )
)
RETURN RowNum - WorkCount

Second stage is to actually count the consecutive days:

Streak = CALCULATE(COUNT(MainTable[Date]),FILTER(MainTable,
MainTable[Employee Name]=EARLIER(MainTable[Employee Name])&&
MainTable[StreakGroup]=EARLIER(MainTable[StreakGroup])))

Does that look correct or should I use another approach?

[Aburar_123]

Hi @WorkHard_ ,

Please try this Calculated column logic,

Desired Streak1 =

var prev_0_date = calculate(MAX('Table'[Date].[Date]),FILTER('Table','Table'[Date]<EARLIER('Table'[Date]) && 'Table'[WorkedFlag]=0))

var next_0_date = calculate(MIN('Table'[Date].[Date]),FILTER('Table','Table'[Date]>EARLIER('Table'[Date]) && 'Table'[WorkedFlag]=0))

return if('Table'[WorkedFlag]<>0,COUNTROWS(FILTER('Table','Table'[Date]>prev_0_date && 'Table'[Date]<next_0_date)))

Thanks

[v-hjannapu]

Hi @WorkHard_,

Thank you  for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank @Ashish_Mathur , for actively participating in the community forum and for the solutions you have been sharing in the community forum. Your contributions make a real difference.

 I also tried few steps on my side, but the results are not matching as expected. Can you please confirm where exactly you are getting stuck. Also, if you can share what exact output you are expecting vs what you are currently getting, it will help us guide you better.

Regards,
Harshitha.

[v-hjannapu]

Hi @WorkHard_,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Harshitha.

[Ashish_Mathur]

Hi,

Where are you stuck and what result are you expecting?

[WorkHard_]

Hi @Ashish_Mathur and @v-hjannapu.
The results expected are in the DesiredStreakGroup and DesiredStreak columns in the original post.

Current incorrect result using the above formulas is:

Employee Name	Date	WorkedFlag	IncorrectStreakGroup	IncorrectStreak
John	8/1/2025	1	0	3
John	8/2/2025	1	0	3
John	8/3/2025	1	0	3
John	8/4/2025	0	1	3
John	8/5/2025	1	1	3
John	8/6/2025	1	1	3
John	8/7/2025	0	2	2
John	8/8/2025	1	2	2
John	8/9/2025	0	3	2
John	8/10/2025	1	3	2
John	8/11/2025	0	4	1
John	8/12/2025	0	5

I'm stuck at calculating the consecutive days in the above sample data and reset the count each time there is a break. I tried the approach I posted there with the StreakGroup and Streak calculated columns but I'm unable to get the correct result.

The correct expected result is posted in the original post and converted to text below:

Employee Name	Date	WorkedFlag	DesiredStreakGroup	DesiredStreak
John	8/1/2025	1	0	3
John	8/2/2025	1	0	3
John	8/3/2025	1	0	3
John	8/4/2025	0
John	8/5/2025	1	1	2
John	8/6/2025	1	1	2
John	8/7/2025	0
John	8/8/2025	1	2	1
John	8/9/2025	0
John	8/10/2025	1	3	1
John	8/11/2025	0
John	8/12/2025	0

John worked consecutevely from 8/1/2025 to 8/3/2025, the result should be 3.
John did not work on 8/4/2025, the result should be blank.
John worked consecutevely from 8/5/2025 to 8/6/2025, the result should be 2.
John did not work on 8/7/2025, the result should be blank.
John worked on 8/8/2025, the result should be 1.
John did not work on 8/9/2025, the result should be blank.
John worked on 8/10/2025, the result should be 1.
John did not work on 8/11/2025, the result should be blank.
John did not work on 8/12/2025, the result should be blank.

[Ashish_Mathur]

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Date", type date}, {"WorkedFlag", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Name", "WorkedFlag"}, {{"T", each List.Sum([WorkedFlag]), type nullable number}, {"A", each _}},GroupKind.Local),
    #"Expanded A" = Table.ExpandTableColumn(#"Grouped Rows", "A", {"Date"}, {"Date"})
in
    #"Expanded A"

Hope this helps.