topic Re: DAX Help - Calculate count of employees scheduled > 12 hours/day for 3 consecutive days in DAX Commands and Tips

DAX Help - Calculate count of employees scheduled > 12 hours/day for 3 consecutive days

yve214 — Fri, 17 Dec 2021 20:36:06 GMT

I have been struggling to get the appropriate measure for this metrics.

They are asking that I provide a list of employees and the hours if they are scheduled for more than 12 hours/day for 3 consecutive days (>36 hours in 3 days). Below is what my raw data looks like, thank you. Please let me know if you have questions happy to clarify, thank you in advance.

Employee ID	Starts	Ends	Employee Type	Start Time	End Time	Hours
10011	12/24/2020	12/24/2020	Full Time	9:00 AM	9:00 PM	12.00
10011	12/25/2020	12/25/2020	Full Time	12:00 PM	6:00 AM	18.00
10011	12/26/2020	12/26/2020	Full Time	9:00 AM	12:00 AM	15.00
10012	1/6/2021	1/6/2021	Full Time	1:00 PM	9:00 PM	8.00
10013	1/8/2021	1/8/2021	Full Time	6:00 AM	12:00 AM	18.00
10015	1/10/2021	1/11/2021	Part Time	8:00 AM	4:00 PM	8.00
10013	1/11/2021	1/11/2021	Full Time	12:00 PM	12:00 AM	12.00
10013	1/12/2021	1/12/2021	Full Time	12:00 PM	12:00 AM	12.00
10013	1/13/2021	1/13/2021	Full Time	12:00 PM	12:00 AM	12.00

Output:

employee count = 2

details:

Employee ID	Total Consecutative Hours
1011	45.00
1013	36.00

Re: DAX Help - Calculate count of employees scheduled > 12 hours/day for 3 consecutive days

Syk — Fri, 17 Dec 2021 21:30:35 GMT

A little tricky with the 3 consecutive days requirement.. Wasn't able to completely work through it right now but hopefully this may help get you started!

Measure = VAR Total_Hours = CALCULATE(sum(Sheet1[Hours]),values(Sheet1[Employee ID])) //VAR period = DATESINPERIOD(Sheet1[Ends],MAX(Sheet1[Ends]),-3,DAY) //Not sure if this will actually be helpful VAR Result = DISTINCTCOUNT(Sheet1[Employee ID]) RETURN if (Total_Hours > 36 ,result)

Re: DAX Help - Calculate count of employees scheduled > 12 hours/day for 3 consecutive days

smpa01 — Sun, 19 Dec 2021 01:53:03 GMT

yve214 can you please try the below measure

Measure2 = VAR _emp = MAX ( Employee[Employee ID] ) VAR _upper = MAX ( Employee[Starts] ) VAR _filt = FILTER ( ALL ( Employee ), Employee[Employee ID] = _emp && Employee[Starts] <= _upper ) VAR _lower1 = MINX ( TOPN ( 3, _filt, Employee[Starts], DESC ), Employee[Starts] ) VAR _lower2 = MINX ( TOPN ( 2, _filt, Employee[Starts], DESC ), Employee[Starts] ) VAR _lower3 = MINX ( TOPN ( 1, _filt, Employee[Starts], DESC ), Employee[Starts] ) VAR _isConsecutive1 = DATEDIFF ( _lower1, _lower2, DAY ) VAR _isConsecutive2 = DATEDIFF ( _lower2, _lower3, DAY ) VAR _sum = IF ( _isConsecutive1 = 1 && _isConsecutive2 = 1, CALCULATE ( SUM ( Employee[Hours] ), FILTER ( ALL ( Employee ), Employee[Starts] >= _lower1 && Employee[Starts] <= _upper && Employee[Employee ID] = _emp ) ) ) RETURN IF ( _sum >= 36, _sum )

Re: DAX Help - Calculate count of employees scheduled > 12 hours/day for 3 consecutive days

yve214 — Sat, 29 Jan 2022 03:40:28 GMT

Hi smpa01,

I must have typed and forgot to reply but thank you so much for your help on this. It worked as expected.