I have the following staff salary table called EmployeeSalaryTbl:
and a table called EmployersNIContributionTbl with the following tax information:
and I’m looking to create a formula to calculate the associated Employers NI Monthly based on their Salary Start Date that will replicate this pseudo code:
(Salary Monthly-Secondary Threshold) * Employers Contribution %
Where Salary Monthly is the row entry in EmployeeSalaryTbl and the Secondary Threshold and Employers Contribution % are from EmployersNIContributionTbl.
How would I go about finding the matching row from the employee start date where the start date is within the Tax Period Start Date and Tax Period End Date in the table EmployersNIContributionTbl?
>Solution :
In case the commented suggestion wasn’t enough, here’s another solution:
=([@[Salary Monthly]]-FILTER(EmployersNIContributionTbl[Secondary Threshold], ([@[Salary Start Date]]>=EmployersNIContributionTbl[Tax Period Start Date])*([@[Salary Start Date]]<=EmployersNIContributionTbl[Tax Period End Date])))*FILTER(EmployersNIContributionTbl[Employers Contribution %], ([@[Salary Start Date]]>=EmployersNIContributionTbl[Tax Period Start Date])*([@[Salary Start Date]]<=EmployersNIContributionTbl[Tax Period End Date]))