Forum Discussion
Calculate Hourly Rate Changes by Amount
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 ...
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 E Table_Final Result
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])
smpa01
Community Champion
Community ChampionAnonymous 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 |
E Table_Final Result
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:
All_Salary = CALCULATE( MAX( R [hourly_rate] ), FILTER( R , R [skill_type] = E [skill_type] && R [company_code] = E [company_code] ) )* R = my Rates TableAlso,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] ) ) )