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: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
Ellen_horton14
New Member

Creating measure between two table to track hours in position per payperiod

‎02-12-2025 12:03 PM

I have 2 tables linked by Emp ID No in a many to many cardinality. I want to be able to look at a specific pay period and see the hours a person worked based on the position, team, division, or branch they were assigned to at the time by comparing the start and End dates in ORG table to the LaborDetail Pay Period dates. I'm very new to anything more complex than point and click in Power BI.

 

LaborDetail

Direct vs OverheadEMP ID NoFull NameRollup HrsLabor HoursWork DatePay Period End Date
OverheadE1985Marty McFlyREG11/28/20252/8/2025
DirectE1985Marty McFlyREG151/29/20252/8/2025
DirectE1985Marty McFlyREG311/23/20251/25/2025
OverheadE1985Marty McFlyREG21/23/20251/25/2025
OverheadE1234MINNIE MOUSEREG311/6/202411/16/2024
DirectE1234MINNIE MOUSEREG1311/6/202411/16/2024
DirectE1234MINNIE MOUSEREG710/26/202311/4/2023
OverheadE1234MINNIE MOUSEREG110/26/202311/4/2023
DirectE5678BlueyREG2811/14/202311/18/2023
OverheadE5678BlueyREG411/14/202311/18/2023
DirectE5678BlueyREG22.56/12/20246/15/2024
OverheadE5678BlueyREG1.56/12/20246/15/2024
DirectE007Bond JamesREG3.258/7/20248/10/2024
OverheadE007Bond JamesREG48/7/20248/10/2024
DirectE007Bond JamesREG21.251/24/20251/25/2025
OverheadE007Bond JamesREG7.251/24/20251/25/2025

 

 

ORG

EMP ID NoPersonGradePositionDivBranchTeamStartEnd
E1234Minnie Mouse12SpecialistDiv AFacilityFRR11/17/20249/30/2025
E5678Bluey13Lead  Div AMed 1Reception5/5/20249/30/2025
E1224Christmas Eve09SpecialistDiv AMed 2OR11/17/20249/30/2025
E007Bond James12SpecialistDiv BSupportSPC10/20/20249/30/2025
E1122Elizabeth Doe13SpecialistDiv BSalesOEA1/26/20259/30/2025
E1985Marty McFly09NurseDiv CMed 2OR5/7/20239/30/2025
E9999Joe Smo14MgrDiv COperationsOTB12/31/20239/30/2025
E1122Elizabeth Doe12SpecialistDiv BSupportAE11/17/20241/25/2025
E1234Minnie Mouse11SpecialistDiv AFacilityFRR5/21/202311/16/2024

 

E1224

Christmas Eve07SpecialistDiv AMed 2TRI5/5/202411/16/2024
E1122Elizabeth Doe09SpecialistDiv BSupportAE6/20/202111/16/2024
E3333Krispie Kreme13Lead  Div BSupportAE5/9/202110/19/2024
E007Bond James11SpecialistDiv BElect TechUMS5/7/202310/19/2024
E5678Bluey13SpecialistDiv AMed 1Clinic10/1/20195/4/2024
E1224Christmas Eve07SpecialistDiv ATech OpsNIT3/26/20235/4/2024
E9999Joe Smo14MgrDiv ATech OpsTOP3/26/202312/30/2023
E1234Minnie Mouse11SpecialistDiv AMaintenance and Services SupportDLA1/30/20225/20/2023
E007Bond James09SpecialistDiv BEnvironmentalENT12/6/20215/6/2023
E1985Mary McFly07LPNDiv CMed 1Clinic1/30/20225/6/2023
Message 1 of 3
204 Views
1 ACCEPTED SOLUTION
Anonymous
Not applicable

‎02-12-2025 06:49 PM

Hi @Ellen_horton14 ,

 

I think you can try code as below to create a measure.

Labor hour base on Period =
CALCULATE (
    SUM ( LaborDetail[Labor Hours] ),
    FILTER (
        LaborDetail,
        LaborDetail[EMP ID No] = MAX ( ORG[EMP ID No] )
            && AND (
                LaborDetail[Pay Period End Date] >= MAX ( ORG[Start] ),
                LaborDetail[Pay Period End Date] <= MAX ( ORG[End] )
            )
    )
)

Result is as below.

vrzhoumsft_0-1739414924655.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Creating measure between two table to track hours in position per payperiod.pbix
Message 2 of 3
169 Views
2 REPLIES 2
Poojara_D12
Super User
Super User

‎02-12-2025 09:36 PM

Hi @Ellen_horton14 

Can you please try this measure:

 

Hours_Worked_By_Assignment = 
VAR SelectedPayPeriod = MAX('LaborDetail'[Pay Period End Date]) -- Get the selected Pay Period
VAR EmpID = SELECTEDVALUE('LaborDetail'[EMP ID No]) -- Get the employee ID
VAR WorkDate = MAX('LaborDetail'[Work Date]) -- Get the Work Date

-- Find matching assignment from ORG table
VAR AssignedRole = 
    CALCULATE(
        MAX('ORG'[Position]), 
        'ORG'[EMP ID No] = EmpID && 
        WorkDate >= 'ORG'[Start] && 
        WorkDate <= 'ORG'[End]
    )

-- Calculate total hours worked for the matching period
VAR TotalHours = 
    CALCULATE(
        SUM('LaborDetail'[Labor Hours]), 
        'ORG'[EMP ID No] = EmpID && 
        WorkDate >= 'ORG'[Start] && 
        WorkDate <= 'ORG'[End]
    )

RETURN 
    IF(NOT(ISBLANK(AssignedRole)), TotalHours, BLANK()) -- Show hours only if assignment exists

 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
Message 3 of 3
163 Views
0
Anonymous
Not applicable

‎02-12-2025 06:49 PM

Hi @Ellen_horton14 ,

 

I think you can try code as below to create a measure.

Labor hour base on Period =
CALCULATE (
    SUM ( LaborDetail[Labor Hours] ),
    FILTER (
        LaborDetail,
        LaborDetail[EMP ID No] = MAX ( ORG[EMP ID No] )
            && AND (
                LaborDetail[Pay Period End Date] >= MAX ( ORG[Start] ),
                LaborDetail[Pay Period End Date] <= MAX ( ORG[End] )
            )
    )
)

Result is as below.

vrzhoumsft_0-1739414924655.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Creating measure between two table to track hours in position per payperiod.pbix
Message 2 of 3
170 Views

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All