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.
Hi all,
I'm having trouble calculating hourly rate changes. I have a "Rates" tables and a "Skills" table that are currently matched at a many-to-many relationship (which I can delete if this is unnecessary). It's possible that I am going about solving this problem entirely wrong, but here's what I have right now.
A simplified version of these tables is as follows:
Rates Table:
Company Code Skill Type Hourly Rate Start Date End Date
1 Craftsman 20.00 1/1/19 7/31/19
1 Craftsman II 21.00 8/1/19 12/31/20
2 Journeyman 20.00 1/1/19 1/31/19
2 Ast. Journeyman 19.00 2/1/19 12/31/20
Skills Table:
Company Code Employee ID Skill Type Start Date End Date
1 1001 Craftsman 1/1/19 7/31/19
1 1001 Craftsman II 8/1/19 12/31/20
2 1002 Journeyman 1/1/19 1/31/19
2 1002 Ast. Journeyman 2/1/19 12/31/20
*Prev Skill *Prev Skill Date *Curr Skill *Prev Rate *Curr Rate
Craftsman 7/31/19 20.00
Craftsman II 21.00
Journeyman 1/31/19 20.00
Ast. Journeyman 19.00
*These are calculated columns I've created. (However, this leaves a lot of blank fields).
Calculated Columns are written as:
My end goal is to calculate the rate changes by amount.
For instance, in the example above employee # 1001 had a $1.00/hour increase, while employee # 1002 had a rate change of -1.00/hour.
Solved! Go to Solution.
@Anonymous hopefully this is what you are looking for
E Table
Company | EmployeeID | Skill | Start Date | End Date |
1 | 1001 | Craftsman | 1/1/2019 | 7/31/2019 |
1 | 1001 | Craftsman2 | 8/1/2019 | 12/31/2020 |
2 | 1002 | Journeyman | 1/1/2019 | 1/31/2019 |
2 | 1002 | Ast.Journeyman | 2/1/2019 | 12/31/2020 |
3 | 1003 | Craftsman | 1/1/2004 | 12/31/2004 |
3 | 1003 | Craftsman2 | 1/1/2005 | 12/31/2005 |
3 | 1003 | Journeyman | 1/1/2006 | 12/31/2006 |
T Table
Company | Skill | Rate | Start Date | End Date |
1 | Craftsman | 20 | 1/1/2019 | 7/31/2019 |
1 | Craftsman2 | 21 | 8/1/2019 | 12/31/2020 |
2 | Journeyman | 20 | 1/1/2019 | 1/31/2019 |
2 | Ast.Journeyman | 19 | 2/1/2019 | 12/31/2020 |
3 | Craftsman | 20 | 1/1/2004 | 12/31/2004 |
3 | Craftsman2 | 25 | 1/1/2005 | 12/31/2005 |
3 | Journeyman | 50 | 1/1/2006 | 12/31/2006 |
Calulated Columns are as following
Rank = // To get a ranking of company-employeeID group based on EndDate RANKX( FILTER(E, EARLIER(E[Company])=E[Company] && EARLIER(E[EmployeeID])=E[EmployeeID]), E[End Date], ,ASC )
All_Salary = LOOKUPVALUE(T[Rate],T[Company],E[Company],T[Skill],E[Skill],T[Start Date],E[Start Date],T[End Date],E[End Date])
Last_Salary = CALCULATE(MAX(E[All_Salary]),FILTER(E, E[Rank]=EARLIER(E[Rank])-1))
Rate_Change/Hour = IF(ISBLANK(E[Last_Salary]),0,[All_Salary]-[Last_Salary])
@Anonymous hopefully this is what you are looking for
E Table
Company | EmployeeID | Skill | Start Date | End Date |
1 | 1001 | Craftsman | 1/1/2019 | 7/31/2019 |
1 | 1001 | Craftsman2 | 8/1/2019 | 12/31/2020 |
2 | 1002 | Journeyman | 1/1/2019 | 1/31/2019 |
2 | 1002 | Ast.Journeyman | 2/1/2019 | 12/31/2020 |
3 | 1003 | Craftsman | 1/1/2004 | 12/31/2004 |
3 | 1003 | Craftsman2 | 1/1/2005 | 12/31/2005 |
3 | 1003 | Journeyman | 1/1/2006 | 12/31/2006 |
T Table
Company | Skill | Rate | Start Date | End Date |
1 | Craftsman | 20 | 1/1/2019 | 7/31/2019 |
1 | Craftsman2 | 21 | 8/1/2019 | 12/31/2020 |
2 | Journeyman | 20 | 1/1/2019 | 1/31/2019 |
2 | Ast.Journeyman | 19 | 2/1/2019 | 12/31/2020 |
3 | Craftsman | 20 | 1/1/2004 | 12/31/2004 |
3 | Craftsman2 | 25 | 1/1/2005 | 12/31/2005 |
3 | Journeyman | 50 | 1/1/2006 | 12/31/2006 |
Calulated Columns are as following
Rank = // To get a ranking of company-employeeID group based on EndDate RANKX( FILTER(E, EARLIER(E[Company])=E[Company] && EARLIER(E[EmployeeID])=E[EmployeeID]), E[End Date], ,ASC )
All_Salary = LOOKUPVALUE(T[Rate],T[Company],E[Company],T[Skill],E[Skill],T[Start Date],E[Start Date],T[End Date],E[End Date])
Last_Salary = CALCULATE(MAX(E[All_Salary]),FILTER(E, E[Rank]=EARLIER(E[Rank])-1))
Rate_Change/Hour = IF(ISBLANK(E[Last_Salary]),0,[All_Salary]-[Last_Salary])
This is fantastic! Thank you.
I only made a couple of changes...
LOOKUPVALUE was still returning an error because of multiple values. So I used:
Last_Salary = CALCULATE(MAX(E[All_Salary]),FILTER(E, E[Rank]=EARLIER(E[Rank])-1))
Needed one more criteria... So I added this to the end...
Last_Salary = CALCULATE (MAX(E [All_Salary]), FILTER (E, E[Rank]=EARLIER(E[Rank])-1 && E[employee_id] = EARLIER(E[employee_id] ) ) )
@Anonymous
You may use LOOKUPVALUE Function to add a calculated column and then refer to this post.
The trouble I'm having with LOOKUPVALUE is that it always returns "A table of multiple values was supplied where a single value was expected."
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |