cancel
Showing results for
Did you mean:
Frequent Visitor

DAX - How to Count the Consecutive Occurrences

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
8 REPLIES 8
Frequent Visitor

Issue not solved yet!

Resolver III

Here is a link to GoogleDrive. I have used some intermediar columns. Hope this works for you

Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Frequent Visitor

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.

Many thanks,

WorkDates tableConsecutive Days DAX

Super User IV

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

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Frequent Visitor

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.

Many thanks,

RelationshipBlank

Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Frequent Visitor

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,

Announcements