topic Re: Continuous Streak in DAX Commands and Tips

Continuous Streak

MichelleRA_24 — Wed, 26 Jun 2024 13:08:14 GMT

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:

Re: Continuous Streak

Anonymous — Thu, 27 Jun 2024 04:17:53 GMT

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.

Re: Continuous Streak

MichelleRA_24 — Thu, 27 Jun 2024 15:43:37 GMT

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?

Re: Continuous Streak

Anonymous — Fri, 28 Jun 2024 06:07:19 GMT

HI,MichelleRA_24

Thank you for your quick reply.

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

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.

Re: Continuous Streak

MichelleRA_24 — Thu, 04 Jul 2024 20:43:22 GMT

Thanks for your time and support.👍