Forum Discussion
Helper IPro Rated Salary
Hello community@
I´m stuck with a DAX formula I need to create and I wonder if any of you know how to do it. I need to calculte the pro rated salary for employees who have a termination date to understand what would the savings amount. I am trying to do two calcs:
1. Calculate the savings comparing the report date or the last date from dcalendar versus the monthly salary. In this case, base on the month number of salary, I need to obtain the rest of the total salary.
This is the data format:
| Employee ID | Report Date | Estimated Exit Date | Actual Exit Date | Annual Salary | Monthly Salary (Per 12) | Saving |
| Mary Silva | 2/28/2023 | 3/31/2023 | 3/31/2023 | $ 1000 | $ 83 | $ 750 |
Does anyone know how to do it? 🤔
Thank you very much!
Hi AnaAlbano ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
Saving = var _a= CALCULATE(SUM('Table'[Monthly Salary (Per 12)]),FILTER(ALL('Table'),'Table'[Employee ID]=MAX('Table'[Employee ID]))) var _b=MONTH(MAX('Table'[Actual Exit Date])) return MAX('Table'[Annual Salary])-_a*_b(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide detailed logic as well as input data and expected output. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much, Anonymous ! It worked for me. The only change I had to do was instead of usinf MAX in return value, use SUM, because when pivotting it, the total amount was only the biggest one.
2 Replies
Hi AnaAlbano ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
Saving = var _a= CALCULATE(SUM('Table'[Monthly Salary (Per 12)]),FILTER(ALL('Table'),'Table'[Employee ID]=MAX('Table'[Employee ID]))) var _b=MONTH(MAX('Table'[Actual Exit Date])) return MAX('Table'[Annual Salary])-_a*_b(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide detailed logic as well as input data and expected output. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AnaAlbanoThank you so much, Anonymous ! It worked for me. The only change I had to do was instead of usinf MAX in return value, use SUM, because when pivotting it, the total amount was only the biggest one.