Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
NelsonRobinson
Regular Visitor

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.

 

NelsonRobinson_0-1692940029123.png

 

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.

 

4 REPLIES 4
NelsonRobinson
Regular Visitor

Sorry first time here, cant see how to attach files?

v-tangjie-msft
Community Support
Community Support

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

 

vtangjiemsft_1-1693188931803.png

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. 

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 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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