Im trying to produce a report to track timesheet entries and chargebility etc.
I have a data source of all timesheet entries, and a dataset with information relating to each employee, charge out rate, department, role etc. Both datasets have an Employee_ID for a relationship, this is fine.
I also have the ability to change the original Employee dataset to suit my needs.
I am struggling to find a way to relate these two datasets if there is a change in roles or department or any change in the Employees dataset. See below shows a role change on the 31/07/23, therefore any timesheet entry prior to that should be as a Data Manager, and anything after should be a Surveyor. But what ever the process, it need to be able handle multiple changes to the employee dataset over time. Ie a role changes multiple times over the year, the timesheet entries still need to relate to the role they were at at the time.
Im looking for suggestions on how to handles this, best way to set up the original Employee dataset, and best way to relate the two.
Hi @NelsonRobinson ,
You can use the LOOKUPVALUE() function to create a calculated column.
RoleAtTime = LOOKUPVALUE(
EmployeeHistory[Role],
EmployeeHistory[Employee_ID], Timesheet[Employee_ID]
)DepartmentAtTime = LOOKUPVALUE(
EmployeeHistory[Department],
EmployeeHistory[Employee_ID], Timesheet[Employee_ID]
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Microsoft Fabric & AI Learning Hackathon
Learn from experts, get hands-on experience, and win awesome prizes.
