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])
v-chuncz-msft
Community Support
Community SupportThe trouble I'm having with LOOKUPVALUE is that it always returns "A table of multiple values was supplied where a single value was expected."