Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
yve214
Helper III
Helper III

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

‎12-17-2021 12:36 PM
Hi there,

 

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 IDStartsEndsEmployee TypeStart TimeEnd TimeHours
1001112/24/202012/24/2020Full Time9:00 AM9:00 PM12.00
1001112/25/202012/25/2020Full Time12:00 PM6:00 AM18.00
1001112/26/202012/26/2020Full Time9:00 AM12:00 AM15.00
100121/6/20211/6/2021Full Time1:00 PM9:00 PM8.00
100131/8/20211/8/2021Full Time6:00 AM12:00 AM18.00
100151/10/20211/11/2021Part Time8:00 AM4:00 PM8.00
100131/11/20211/11/2021Full Time12:00 PM12:00 AM12.00
100131/12/20211/12/2021Full Time12:00 PM12:00 AM12.00
100131/13/20211/13/2021Full Time12:00 PM12:00 AM12.00

 

Output:

employee count = 2

details: 

Employee IDTotal Consecutative Hours
101145.00
101336.00

 

Message 1 of 4
586 Views
0
1 ACCEPTED SOLUTION
smpa01
Super User
Super User

‎12-18-2021 01:12 PM

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

 

 

 

 

smpa01_0-1639878753945.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

_filteringOnConescutiveCondition+TOPN.pbix
Message 3 of 4
475 Views
0
3 REPLIES 3
smpa01
Super User
Super User

‎12-18-2021 01:12 PM

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

 

 

 

 

smpa01_0-1639878753945.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
_filteringOnConescutiveCondition+TOPN.pbix
Message 3 of 4
476 Views
0
yve214
Helper III
Helper III
In response to smpa01

‎01-28-2022 07:40 PM

Hi @smpa01,

 

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

Message 4 of 4
387 Views
0
Syk
Super User
Super User

‎12-17-2021 01:30 PM

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)

 

Syk_0-1639776574792.png

 

Message 2 of 4
539 Views
0

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All