March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
@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 )
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!
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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] )
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
To learn more about Power BI, follow me on Twitter or subscribe 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:
Please advise if I missed something?
Many thanks,
Did you make a 1:Many relationship between WorkDates and Work tables?
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe 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,
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
To learn more about Power BI, follow me on Twitter or subscribe 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,
Did this solution ever work?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
23 | |
16 | |
12 | |
9 | |
7 |
User | Count |
---|---|
38 | |
32 | |
28 | |
12 | |
11 |