cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Muhannadtaghi
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, 🙂

 

 
DateEmployee IDEmployee NameConsecutive Days
1/1/20200011Sam 
1/2/20200012Jim 
1/3/20200013Jones 
1/4/20200013Jones2
1/5/20200011Sam 
1/6/20200014Joseph 
1/7/20200011Sam 
1/8/20200011Sam2
1/9/20200011Sam3
1/10/20200012Jim 
1/11/20200014Joseph 
1/12/20200013Jones 
1/13/20200013Jones2
8 REPLIES 8
Muhannadtaghi
Frequent Visitor

Issue not solved yet!

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
Super User IV
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 ) )

 

consec.png

 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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 tableWorkDates tableConsecutive Days DAXConsecutive Days DAX

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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, 

 

RelationshipRelationshipBlankBlank

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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, 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors