Continuous Streak

[MichelleRA_24]

Hi to all!

I need your help to create a formula to mark the initial date of each group of consecutive dates and maintain that same date for all dates within each group. It is necessary to use a formula that identifies the start of each group of consecutive dates and marks that date for all rows within the group.

The idea is in the "Vacations Start Date" column, dates are listed, some consecutive and others not.

The "If days" column determines if each date is consecutive with the one above: it marks 0 if not consecutive, and 1 if consecutive.

The "Part" column indicates that for consecutive dates, it shows the same start date where the pattern begins at zero.

The "Count Days" column displays the number of consecutive days, excluding weekends.

Lastly, the "Consecutive days" column repeats the total count of consecutive days for each row

I only have two columns, "Vacations Star Date"  and "Return" Could you please guide me on creating the others?

Expected result:

[v-linyulu-msft · Accepted solution]

HI,@MichelleRA_24 

Regarding the issue you raised, my solution is as follows:

1. I have created the following table and the column names and data are the data you have given:

2.First, we need to add a sequence to the powerquery:

3.Secondly, use the following calculation column to determine whether it is continuous:

IF days = 
VAR currentday1=[Vacations Start Date]
VAR predate=CALCULATE(MAX('Table'[Vacations Start Date]),FILTER('Table','Table'[Vacations Start Date]<currentday1))
RETURN IF(predate+1=currentday1,1,0)

4.Then, relying on the previous calculation column, the grouping column is generated:

group = 
VAR CurrentIndex1 = [Index]
VAR PreviousIndex1= CALCULATE(MAX('Table'[Index]), FILTER('Table', 'Table'[Index]<=CurrentIndex1 && 'Table'[IF days]=0))
RETURN PreviousIndex1

5.You can then proceed to create calculation columns that meet your needs:

RETURN = 'Table'[Vacations Start Date]+1

Count days = 
VAR CurrentIndex2 = [Index]
VAR PreviousIndex = CALCULATE(MAX('Table'[Index]), FILTER('Table', 'Table'[Index]<=CurrentIndex2 && 'Table'[IF days]=0))
RETURN IF(ISBLANK(PreviousIndex), 0, (CurrentIndex2 - PreviousIndex))+1

Consecutive days = 
CALCULATE(MAX('Table'[Count days]),FILTER('Table','Table'[group]=EARLIER('Table'[group])))

Part = 
CALCULATE(MIN('Table'[Vacations Start Date]),FILTER('Table','Table'[group]=EARLIER('Table'[group])))

6.Here's my final result, which I hope meets your requirements.

It may be noted that November 27 in your case is not consecutive from the previous date, so the expected output result you provided may be wrong in my understanding of your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-linyulu-msft · Accepted solution]

HI,@MichelleRA_24 

Regarding the issue you raised, my solution is as follows:

1. I have created the following table and the column names and data are the data you have given:

2.First, we need to add a sequence to the powerquery:

3.Secondly, use the following calculation column to determine whether it is continuous:

IF days = 
VAR currentday1=[Vacations Start Date]
VAR predate=CALCULATE(MAX('Table'[Vacations Start Date]),FILTER('Table','Table'[Vacations Start Date]<currentday1))
RETURN IF(predate+1=currentday1,1,0)

4.Then, relying on the previous calculation column, the grouping column is generated:

group = 
VAR CurrentIndex1 = [Index]
VAR PreviousIndex1= CALCULATE(MAX('Table'[Index]), FILTER('Table', 'Table'[Index]<=CurrentIndex1 && 'Table'[IF days]=0))
RETURN PreviousIndex1

5.You can then proceed to create calculation columns that meet your needs:

RETURN = 'Table'[Vacations Start Date]+1

Count days = 
VAR CurrentIndex2 = [Index]
VAR PreviousIndex = CALCULATE(MAX('Table'[Index]), FILTER('Table', 'Table'[Index]<=CurrentIndex2 && 'Table'[IF days]=0))
RETURN IF(ISBLANK(PreviousIndex), 0, (CurrentIndex2 - PreviousIndex))+1

Consecutive days = 
CALCULATE(MAX('Table'[Count days]),FILTER('Table','Table'[group]=EARLIER('Table'[group])))

Part = 
CALCULATE(MIN('Table'[Vacations Start Date]),FILTER('Table','Table'[group]=EARLIER('Table'[group])))

6.Here's my final result, which I hope meets your requirements.

It may be noted that November 27 in your case is not consecutive from the previous date, so the expected output result you provided may be wrong in my understanding of your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MichelleRA_24]

Hi Leroy!

Your solution is very good, I´m impressed, thank you very much for dedicating that time. Regarding what you mentioned about November 27, it is correct to count Monday the 27th as consecutive from Friday, as weekends should not be counted as part of consecutive days. That's why the employee took 6 consecutive days despite having a weekend in between. Can this be corrected in the formula?

[v-linyulu-msft · Accepted solution]

HI,@MichelleRA_24 

Thank you for your quick reply.

1.The following is my updated calculation column, the rest remains unchanged:

IF days = 
VAR currentday1=[Vacations Start Date]
VAR predate=CALCULATE(MAX('Table'[Vacations Start Date]),FILTER('Table','Table'[Vacations Start Date]<currentday1))
RETURN IF(NETWORKDAYS(predate,currentday1)=2,1,0)

2.Here's my final result, which I hope meets your requirements

3.Here are my related documents, I hope to help you:

NETWORKDAYS function (DAX) - DAX | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MichelleRA_24]

Thanks for your time and support.👍