DAX - How to Count the Consecutive Occurrences

[Muhannadtaghi]

Hi There;

I would like to count the consecutive worked days for a group of employees, I have a table of employee work date, employee ID, employee name as shown below and I wont to identify who worked 2 or more days in a row.

I need your help to suggest a DAX that count the consecutive occurrence only, for example, in my table below the employee Jones worked 2 days in a row in the 3rd and 4th days of the month, this should be one occurrence and he worked 2 days in a row in the 12th and 13th days of the month, that should be another separate occurrence.

All that being said, I need a DAX for counting the consecutive occurrences separately for each employee but not accumulatively.

Please feel free to contact me with any questions/ clarifications.

Many thanks, 🙂

Date	Employee ID	Employee Name	Consecutive Days
1/1/2020	0011	Sam
1/2/2020	0012	Jim
1/3/2020	0013	Jones
1/4/2020	0013	Jones	2
1/5/2020	0011	Sam
1/6/2020	0014	Joseph
1/7/2020	0011	Sam
1/8/2020	0011	Sam	2
1/9/2020	0011	Sam	3
1/10/2020	0012	Jim
1/11/2020	0014	Joseph
1/12/2020	0013	Jones
1/13/2020	0013	Jones	2

[smpa01]

@Muhannadtaghi  you would need an rowNum/Index column in the dataset and then you can write a measure like this

prev = 
VAR prevIndex =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = MAX ( 'Table'[Index] ) - 1 )
    )
VAR prevId =
    CALCULATE (
        MAX ( 'Table'[Employee ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = prevIndex )
    )
VAR cond =
    IF ( MAX ( 'Table'[Employee ID] ) <> prevId, MAX ( 'Table'[Index] ) ) 
RETURN
    cond



consecutiveDaysCount = 
VAR currDate =
    MAX ( 'Table'[Date] )
VAR curr =
    MAX ( 'Table'[Index] )
VAR __topN =
    MAXX (
        TOPN (
            1,
            FILTER (
                SUMMARIZE (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Employee Name] = MAX ( 'Table'[Employee Name] )
                            && 'Table'[Index] <= [prev]
                    ),
                    'Table'[Index]
                ),
                [Index] <= curr
            ),
            [Index], DESC
        ),
        [Index]
    )
VAR test =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = __topN )
    ) 
--var debugger = TOCSV(__lastDate, -1, ",")
RETURN
    DATEDIFF ( test, currDate, DAY )

[david_crisci]

Thank you for this, this is already a great explanation. I am trying to do something similar, except the Index column won't work because, say there are multiple people who work on the same day - this is what my data is like. For example:

Date -- Name 

1/1/23 -- John

1/2/23 -- Sam

1/3/23 -- Sam

1/3/23 -- Dave

1/4/23 -- Sam

For the bottom record, we would want Sam to show 3 days, but if you use the Index, it wouldn't catch it because of the "Dave" record. Any ideas to amend above to calculate the consecutive days for this case?? Thank you!

[Muhannadtaghi]

Issue not solved yet!

[JW_van_Holst]

Here is a link to GoogleDrive. I have used some intermediar columns. Hope this works for you
https://drive.google.com/file/d/1lIus89C5W_VpzmKptRMHeXJeHtiwckul/view?usp=sharing 

[mahoneypat]

Here is one approach to do this.  It does a calculation to find the last date the employee did not work, so I needed to create a Date table called "WorkDates" that has all dates with WorkDates = CALENDAR(MIN(Work[Date]), MAX(Work[Date])).  But you can adapt this for any Date table.

Number Consecutive Days =
VAR selecteddate =
SELECTEDVALUE ( 'WorkDates'[Date] )
VAR lastnotworkeddate =
CALCULATE (
MAXX (
FILTER ( WorkDates, ISBLANK ( CALCULATE ( COUNTROWS ( 'Work' ) ) ) ),
WorkDates[Date]
),
WorkDates[Date] < selecteddate
)
VAR diff =
DATEDIFF ( lastnotworkeddate, selecteddate, DAY )
RETURN
IF ( COUNTROWS ( 'Work' ) > 0, IF ( diff > 1, diff ) )

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Meagan_Lea]

Hi @mahoneypat , I am trying to use your measure above and it is not working. I am wondering if it is due to the last statement? 

IF ( COUNTROWS ( 'Work' ) > 0, IF ( diff > 1, diff ) )

Is the true statement missing from the first part and the false statement missing from the second statement?

IF ( COUNTROWS ( 'Work' ) > 0 [True missing], IF ( diff > 1, diff ), [False missing] )

[mahoneypat]

With the IF function, if you don't provide a false option, it returns blank. The expression is complete, accepting the default BLANK() for both falses.

Pat

[Muhannadtaghi]

Hi Mahoneypat;

Thanks a lot for your support. Unfortunately, it did not work, it was showing blank.

Below are all the steps that I did to test it out:

• I renamed the same example table that I shared with you to “Work” to align with the date table and DAX
• Created the “WorkDates” table
• Applied the DAX exactly as you post it, please see below.

Please advise if I missed something?

Many thanks,

WorkDates tableConsecutive Days DAX

[mahoneypat]

Did you make a 1:Many relationship between WorkDates and Work tables?

Regards,

Pat

[Muhannadtaghi]

Hi mahoneypat; 

Thanks for your reply, I tried all 4 different types of cardinalities and all types of cross filter directions, but still showing blank.

Also, I used the DAX in measure and calculation column, but nothing changed.

Please advise.

Many thanks, 

RelationshipBlank

[mahoneypat]

My bad for not clarifying I was using the measure in a table with WorkDates[Date] and Work[EmployeeID].  To use it in a card, use this measure that references the first one.

Total Consecutive Days =
SUMX (
SUMMARIZE ( 'Work', WorkDates[Date], 'Work'[Employee ID] ),
[Number Consecutive Days]
)

And the relationship needs to be changed back too.  1:Many from WorkDates to Work (Filter going 1:many too).

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Muhannadtaghi]

Hi Pat;

Thanks for the comment, could you please share with me the pbix file so I can see how you did it?

Many thanks for the support, 

[lstevens]

Did this solution ever work?