topic Re: Calculation for consecutive work days for employee in DAX Commands and Tips

Calculation for consecutive work days for employee

babyjb1979 — Tue, 29 Aug 2023 16:55:15 GMT

Hi all,

I am creating a measure to calculate the consecutive work days for every employee. The table has columns including [Employee ID],[Work Date]

I extact an employee as test, I already make it using following DAX: (One employee's data as a new data source)

Consecutive Work Days =

var tb1=MAX('Date'[Date])

var tb2=ALL('Date'[Date])

var tb3=ALL('Work'[Work Date])

var tb4=EXCEPT(tb2,tb3)

var tb5=MAXX(FILTER(tb4,[Date]<=tb1),[Date])

Return

INT(tb1-tb5)

Now, I want to apply the DAX to all employees:

Consecutive Work Days 1 =

CALCULATE(

var tb0=SUMMARIZE('Employee Transaction','Employee Transaction'[Work Date])

var tb1=MAX('Date'[Date])

var tb2=ALL('Date'[Date])

var tb3=EXCEPT(tb2,tb0)

var tb4=MAXX(FILTER(tb3,[Date]<=tb1),[Date])

Return

INT(tb1-tb4),

FILTER('Employee Transaction','Employee Transaction'[Employee Number]=EARLIER('Employee Transaction'[Employee Number]))

)

I don't know what the reason leading to the error. Could you please help me out?

Thank you in advance!

Branko

Re: Calculation for consecutive work days for employee

babyjb1979 — Thu, 31 Aug 2023 14:58:15 GMT

No one knows the solution?

Re: Calculation for consecutive work days for employee

technolog — Sun, 24 Sep 2023 08:09:56 GMT

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.