Need help creating a formula to give employee role at the time of the DOS in timesheets

[Ejackson02]

Roles Data

Employee ID	Start Date	End Date	Job Title
Kate Willson	1/1/2023	6/3/2023	Deputy Director
Kate Willson	6/4/2023	12/31/2040	Director
Jane Doe	1/1/2023	6/4/2023	Policy Consultant
Jane Doe	6/4/2023	12/31/2040	Senior Policy Consultant
Clay Brown	1/1/2023	6/5/2023	HR Generalist
Clay Brown	6/4/2023	12/31/2040	Senior HR Generalist
Kelly Price	1/1/2023	6/6/2023	Junior Policy Consultant
Kelly Price	6/4/2023	12/31/2040	Policy Consultant

Timesheet Data

DOS	Employee
1/1/2023	Kate Willson
6/4/2023	Kate Willson
6/22/2023	Kate Willson
6/23/2023	Kate Willson
3/6/2023	Jane Doe
3/7/2023	Jane Doe
6/22/2023	Jane Doe
6/23/2023	Jane Doe
1/1/2023	Clay Brown
6/4/2023	Clay Brown
6/22/2023	Clay Brown
6/23/2023	Clay Brown
3/6/2023	Kelly Price
3/7/2023	Kelly Price
6/22/2023	Kelly Price
6/23/2023	Kelly Price

I would like the role at the time of the DOS as a third column in the timesheet data. 

[ryan_mayu · Accepted solution]

@Ejackson02 

you can use DAX to create a column

jobtitle = maxx(FILTER('role',role[Employee ID]=timesheet[Employee]&&timesheet[DOS]>='role'[Start Date]&&timesheet[DOS]<=role[End Date]),role[Job Title])

pls see the attachment below

[Ejackson02]

Hello Ryan,

Thank you for your response. I was able to create a column and duplicate your results, which produced the correct title for the employee on the given DOS. I’m new to Power BI and have a lot to learn. Thank you for your prompt reply!

[ryan_mayu]

you are welcome

[DOLEARY85]

Hi,

Make sure your tables are connected by an Employee ID field and then you could try a simple measure like the below:

Measure =

CALCULATE(

    MAX('Roles Table'[Job Title]),

    FILTER(

        'Roles Table',

        'Roles Table'[Start Date] <= MAX('Timesheet Table'[DOS]) &&

        'Roles Table'[End Date]>= MAX('Timesheet Table'[DOS])

    )

)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Ejackson02]

Thank you for such a quick reply! Unfortunately, I could not get the measure to provide the correct titles. The measure would only provide Senior Policy Consultant and Policy Consultant for everyone. I’m new to using Power BI, so I may have transposed a step. I’ll keep trying to see if I can get the correct results. Again, thank you for your reply!