Many Criterias Calculated columns

Hello Everyone

first of all this is a sample for the expected answer but manually

 I'm trying to build HR project and I’m going to get to subject directly

For the date calculated column (PreviousRegAddDate) I wanna do the following

1. For each row in FTransactions2 Table we wanna check the RegDecCodeNum column if it contains a decision code or not ( this column is related to dtblRegularAdds Dimension Table) and if current row doesn’t contain a value in RegDecCodeNum column in FTransactions2 table then calculated column should return blank cell or null value.
2. If current row contains a value in RegDecCodeNum in FTransactions2 table then we wanna check the corresponding EmpID value in FTransactions2 table also
3. Now we should go back in all previous rows in FTransactions2 Table and filter to only EmpID extracted from step 2
4. Then from the previous filter in step 3 we filter the same FTransactions2 table for the second time by filter it only filled rows with RegDecCodeNum column means it’s filter based on another filter
5. Then let’s just say the filter resulted 2 rows of data in FTransactions2 table to this particular EmpID then we should get the nearest RegDecCodeNum ID and use the relationship between dtblRegularAdds and FTransactions2 to return the corresponding DecImpDate from the dtblRegularAdds to be our final calculated column result.
6. If the two filters we applied in steps 3, 4 gave us zero or no rows to display then simply the result of our calculated column with be the “Decision Apply Date” column in FTransactions2 table or in dtblassignmentDec table

For the base wage calculated column (RegAddBaseWage) I wanna do the following :

1. For each row in FTransactions2 Table we wanna check the RegDecCodeNum column if it contains a decision code or not ( this column is related to dtblRegularAdds Dimension Table) and if current row doesn’t contain a value in RegDecCodeNum column in FTransactions2 table then calculated column should return blank cell or null value.
2. If current row contains a value in RegDecCodeNum in FTransactions2 table then we wanna check the corresponding EmpID value in FTransactions2 table also
3. Now we should go back in all previous rows in FTransactions2 Table and filter to only EmpID extracted from step 2
4. Then from the previous filter in step 3 we filter the same FTransactions2 table for the second time by filter it only filled rows with RegDecCodeNum column means it’s filter based on another filter
5. Then let’s just say the filter resulted 2 rows of data in FTransactions2 table to this particular EmpID then we should get the previous value in in this calculated column itself and for the first value in this column so it should be the Wage column  from dtblBasicEmpInfo table
6. If for Current EmpID there is any calculated wage during this year then we should return the last nearest wage from maximum wage of Promotion, Encouragement, exceptional Calculated columns which we are going to calculate later

Now the most interesting part which is third calculated column (ModifiedWageAfterAddingRegularAdd ) instead of a measure coz it’s easier like this to me to explain we can make it as follows:

1. First of all we should compare between the current DecImpDate column in dtblRegularAdds table with the date generated in the calculated column PreviousRegAddDate in FTransactions2 table and if this period is less than a year then the measure should returns the RegAddBaseWage calculated column in FTransactions2 as it’s without any modification.
2. Second condition if step 1 passes then we should check through the current year in LateralCourtDecCode column in FTransactions2 table if there is a value in this column assigned to any employee then he also shouldn’t get the regular add for this year.
3. If the employee passes the previous step then we should calculate this year’s regular add by multiplying corresponding PromotionRate column in dtblRegularAdds dimension table in the calculated base wage column RegAddBaseWage in FTransactions2 table
4. Then we should compare the add amount calculated in previous step 3 with the Lower Limit column in dtblRegularAdds table and add the bigger amount or value from them to our current base wage in RegAddBaseWage column in FTransactions2 table

That’s it for the first decision type of my project if there is any mysterious points within my explanation just tell me 

i'm gonna use power pivot and power query in excel and VBA for forms and reports printing part

Thx to everyone in advance

I’ll attach the sample pbix file 

Sample