Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two date tracked tables (Positions and Assignments) to allow tracking of changes to each. The Positions table contains a Position ID, effective start date and effective end date). The assignment table contains Assignment ID, effective start date and effective end date (for the assignment). It also references contains the Position ID as part of the record.
I want to be able to display the applicable position and assignment information for a given date. Since the start and end dates don't correlate, I'm having a problem trying to create a relationship. Any suggestions would be greatly appreciated.
Hi @rujimenez,
I want to be able to display the applicable position and assignment information for a given date.
How do you want to display the applicable position and assignment information for a given date, is it by the given date = effective start/end date or the given date is between effective start and end date?
Could you be more precisely with your requirements by posting some sample data with your expected result?
Regards
The result will contain fields from records which are active between the start and end dates of both tables. A simple example:
Positions:
Position ID Start Date End Date Position Name
1 02/15/2013 11/10/2014 Original Position Name
1 11/11/2014 03/21/2015 Position Name change 1
1 03/22/2015 Position Name change 2
2 10/15/2014 Another Position Name
Assignments:
Asg ID Start Date End Date Position ID Employee ID Pay Grade
20 10/10/2014 01/15/2015 1 233333 11
20 01/16/2015 11/15/2015 1 233333 12
20 11/16/2015 01/20/2016 1 233333 13
23 01/21/2016 2 2333333 13
On 02/15/2015
Employee ID Position Name Assignment ID Pay Grade
233333 Position Name change 1 20 12
0n 02/22/2016
Employee ID Position Name Assignment ID Pay Grad
233333 Position Name change 2 23 13
The result would be a snapshot of all employees and their assignments on a given date.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |