Anonymous

Not applicable

7 years ago

Solved

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 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??

Need Help

smpa01

7 years ago

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

4 Replies

smpa01

Community Champion

7 years ago

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
Not applicable
7 years ago
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
Community Support
7 years ago
Anonymous

You may use LOOKUPVALUE Function to add a calculated column and then refer to this post.
- Anonymous
  Not applicable
  7 years ago
  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."

Forum Discussion

Calculate Hourly Rate Changes by Amount

4 Replies

Related content

Calculate YoY change

Calculate Hourly Rate

Hourly average sales

Hourly Refresh?

Calculating Quarter over Quarter Change