Forum Discussion

NelsonRobinson's avatar
NelsonRobinson
Regular Visitor
3 years ago

Relationships determined by date

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.

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 

    • NelsonRobinson's avatar
      NelsonRobinson
      Regular Visitor
      3 years ago

      See below link for example files.

       

      https://BB.oper8cloud.com.au/invitations?share=ec490229468a75205b48 

       

      See the Example_Data.xlsx.

      • The sheet 'STAFF DATA'  is what i have a bit of power over the creation of, so suggestions on how to handle this would be good. This contains an EmployeeCode which does not relate to the timesheets. Note that any change happens to an employee, their EmployeeCode stays the same.
      • The Empoyee_ID sheet is needed as an intermediate relationship. The STAFF_DATA has an EmployeeCode, and the TIMESHEETS, has an EmployeeID. The Employee_ID sheet has the relationship of these two.
      • TIMESHEETS, ive added a couple weeks of data, and has EmployeeID for relation.
      • Expectation. What i am trying to produce. Note the change in Role and other information to Ben Dawes, and Nelson Robinson

      In the STAFF DATA you can see a change in information for Ben Dawes on the 14/7/2023, and a change for Nelson Robinson on the 24/7/2023. In my expectation sheet, you can see these changes take place on the correct dates.

      Let me know if you need further clarification.

       

      Table below showing expectation. Nelson Robinson Changed roles on 24/7/2023

      TimesheetLineIDDateStartTimeStopTimeJobEmpoyeeIDPreferredNameLastNameEmployeeCodeJobTitleChargeability%CapabilityNotes
      5865435421/07/20237:00:00 AM4:00:00 PMJob 325910NelsonROBINSON1422SURVEYOR95SiteMap 
      5865435521/07/20237:00:00 AM4:00:00 PMJob 425902MaxSCOTT1461PLANNER50Planning 
      5865435624/07/20237:00:00 AM4:00:00 PMJob 127277BenDAWES1516REGIONAL MANAGER50Corperate 
      5865435724/07/20237:00:00 AM4:00:00 PMJob 225841AshNAIDU1157ASSISTANT0SiteMAP 
      5865435824/07/20237:00:00 AM4:00:00 PMJob 325910NelsonROBINSON1422DATA MANAGER50SiteDENelson Robinson Changed roles here
      5865435924/07/20237:00:00 AM4:00:00 PMJob 425902MaxSCOTT1461PLANNER50Planning 
      5865436025/07/20237:00:00 AM4:00:00 PMJob 127277BenDAWES1516REGIONAL MANAGER50Corperate 
      5865436125/07/20237:00:00 AM4:00:00 PMJob 225841AshNAIDU1157ASSISTANT0SiteMAP 
      5865436225/07/20237:00:00 AM4:00:00 PMJob 325910NelsonROBINSON1422DATA MANAGER50SiteDE