historical
1 Topic
Salary Table Vs Bonus Table Salary
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!Solved1.3KViews0likes3Comments