Salary Table Vs Bonus Table Salary

[cristianjsheld]

Hi, 

I've got a table with the historical data on the salaries, with their corresponding effective date, and another with bonuses amount with their payment date. I need to create a measure/column/table (I don't know which could be the best way to do it) to know from the bonuses paid which is the percentage over salary. Something like this:

Salary table:

Bonus table:

The logic should work from the bonus amount relating to the payment date and the compensation effective date, where the bonus will be calculated over the latest salary based on the date (the bonus paid on 02/15/2020 will be calculated over the salary with a compensation date 07/01/2019 as it was the latest salary prior the bonus payment date). I have a calendar table where the payment date and the compensation date are connected.

Employee	Payment date	Discretionary Bonus Amount	Base Salary Date	Pay Rate	Percentage from salary
415	2/15/2020	3900	7/1/2019	156,000	2.50%
415	2/15/2021	2000	7/1/2019	156,000	1.28%
415	2/15/2022	6000	1/1/2022	165,000	3.64%

Thanks!

[cristianjsheld]

Amazing @Greg_Deckler . It worked on the first try.

[Greg_Deckler]

@cristianjsheld Kudo's to you for providing a clear explanation, sample data and expected output! Helps soooooooo much!!

[Greg_Deckler · Accepted solution]

@cristianjsheld Maybe:

Measure =
  VAR __Employee = MAX('Bonus'[Employe #])
  VAR __Bonus = MAX('Bonus'[Bonus Amount])
  VAR __PayDate = MAX('Bonus'[Payment Date])
  VAR __LastCompDate = MAXX(FILTER(ALL('Salary'),'Salary'[Employee #] = __Employee && 'Salary'[Compensation: Date] <= __PayDate), 'Salary'[Compensation: Date])
  VAR __PayRate = MAXX(FILTER(ALL('Salary'), 'Salary'[Employee #] = __Employee && 'Salary'[Compensation: Date] = __LastCompDate ), 'Salary'[Pay rate])
RETURN
  DIVIDE(__Bonus, __PayRate,0)