Calculate Hourly Rate Changes by Amount

[Anonymous]

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: 

Current Skill = IF( Skills_Table[eff_end_date]> TODAY() , Skills_Table [skill_type] , BLANK() )  

(This tells me if their skill type is current or not.)

Prev Skill Date = CALCULATE( MAX( Skills_Table [eff_end_date] ) , FILTER( FILTER( Skills_Table , Skills_Table[eff_end_date] < TODAY() ) , Skills_Table[employee_id] = EARLIER( Skills_Table[employee_id] ) ) )

Previous Skill = IF ( Skills_Table [Prev Skill Date] = Skills_Table [eff_end_date] , Skills_Table[skill_type] , BLANK() )

Prev Rate = CALCULATE( FIRSTNONBLANK ( Rates_Table [hourly_rate] , 1 ), FILTER ( FILTER ( Rates_Table, Rates_Table [skill_type] = Skills_Table [Previous Skill] ), Rates_Table [company_code] = Skills_Table [company_code] ) )

Curr Rate = CALCULATE( FIRSTNONBLANK ( Rates_Table [hourly_rate] , 1 ), FILTER( FILTER( Rates_Table, Rates_Table[skill_type] = Skills_Table[Current Skill] ), Rates_Table[company_code] = Skills_Table [company_code] ) )

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. 

I'm stuck at this point... trying to calculate the difference between the previous rate and the current rate. Any suggestions on how I could better go about solving this?? 

[smpa01 · Accepted 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

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

[Anonymous]

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 Table

Also, 

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

[v-chuncz-msft]

@Anonymous 

You may use LOOKUPVALUE Function to add a calculated column and then refer to this post.

[Anonymous]

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."