topic Re: DAX - How to Count the Consecutive Occurrences in DAX Commands and Tips

DAX - How to Count the Consecutive Occurrences

Muhannadtaghi — Thu, 28 May 2020 21:25:46 GMT

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

Re: DAX - How to Count the Consecutive Occurrences

mahoneypat — Thu, 28 May 2020 23:31:58 GMT

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

Re: DAX - How to Count the Consecutive Occurrences

Muhannadtaghi — Fri, 29 May 2020 17:01:53 GMT

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,

Re: DAX - How to Count the Consecutive Occurrences

mahoneypat — Sat, 30 May 2020 13:02:18 GMT

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

Regards,

Pat

Re: DAX - How to Count the Consecutive Occurrences

Muhannadtaghi — Sat, 30 May 2020 17:03:33 GMT

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,

Re: DAX - How to Count the Consecutive Occurrences

mahoneypat — Sat, 30 May 2020 18:47:18 GMT

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

Re: DAX - How to Count the Consecutive Occurrences

Muhannadtaghi — Sun, 31 May 2020 01:55:23 GMT

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,

Re: DAX - How to Count the Consecutive Occurrences

Muhannadtaghi — Wed, 03 Jun 2020 17:42:01 GMT

Issue not solved yet!

Re: DAX - How to Count the Consecutive Occurrences

JW_van_Holst — Sun, 07 Feb 2021 16:30:09 GMT

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

Re: DAX - How to Count the Consecutive Occurrences

lstevens — Thu, 26 Aug 2021 16:59:50 GMT

Did this solution ever work?

Re: DAX - How to Count the Consecutive Occurrences

Meagan_Lea — Mon, 13 Feb 2023 07:06:44 GMT

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] )

Re: DAX - How to Count the Consecutive Occurrences

mahoneypat — Wed, 15 Feb 2023 15:38:45 GMT

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

Re: DAX - How to Count the Consecutive Occurrences

smpa01 — Wed, 15 Feb 2023 17:36:32 GMT

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 )

Re: DAX - How to Count the Consecutive Occurrences

Anonymous — Fri, 25 Aug 2023 19:48:59 GMT

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!