help with multi criteria dependant and independant calculated many columns

[Islam]

hello every one 

i'm a beginner power pivot and dax user

i'm trying to make a HR project and it's employee based project

each decision type has a linked fact table and to apply each decision type wether it's numeric or organizational decision

i built the fact table to hold only the codes for each decision dimension table

what i wanna do is

1- create a calculated column to get the base wage for each employee mentioned in the fact table 

2- for each dimension decision table i wanna add three calculated columns to the fact table first column to hold the decision date and the second column to hold the nearest previous wage and the third column of course to hold the new calculated wage after applying different date and other conditions for example if an employee with assigning date of 2/8/2014 with base wage at 700 then for regular add at 1/7/2015 to apply for this employee he should be actually working for one year difference between earliest assigning or period add decision date and also his add will be 7% of previous nearest wage or 65 pound which is bigger and to apply this add he should not get any punishment during this year difference so to get this wage i have to consider punishment table and assigning table and period add table and the regular add table itself so finally i can get a report for each type of decisions for each employee separately 

3- at the end of the fact table i wanna add a final wage calculated column to get the mage wage for each employee for each date to create a final summerized report at the granularity of employee name only

finally i'm gonna build this using power pivot DAX and Excel VBA to enter data and summerize and extract reports of of entered data 

Any help would be highly appreciated

 v-shex-msft

Here is the  Sample File 

[v-shex-msft · Accepted solution]

Hi @Islam,

#1, This part can be achieved to use lookupvalue function for calculating with aggregate function and filters.

Introducing LOOKUPVALUE 

#2, Current power bi does not support doing recursion calculation in DAX formulas. You can simple cumulative records but it not able to invoke the previous calculation result in the same expressions. 

For your scenario, I'd like to suggest you only store the rolling wage [base x (100% + unit x offset rate)] and move the reduced wage part to a standalone table.

#3, I think you can extract this part to create a summarize table with the up-to-date employee and wage information.

BTW, Dax on excel sheets support recursion, perhaps you can consider moving these calculations on your excel sheet side and import the result data tables to power bi desktop for visualization and report designing.

Notice: power bi is a report tool, it existed the dax/m query that can be used to calculate, but the complex calculation and expression should obviously affect and reduce the performance.

Optimization guide for Power BI 

Regards,
Xiaoxin Sheng

[Islam]

thx sir for this great tipe i'll consider this