Many Criterias Calculated columns

[Islam]

Hello Everyone

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

		اسلام احمد منصور احمد 1	اسلام احمد منصور احمد 2
30/06/2014	assignment wage	275
02/08/2014	assignment wage		700
30/06/2015	post exceptional add wage	288.75	735
01/07/2015	post regular add wage	353.75	735
01/07/2016	post regular add wage	418.75	800
01/07/2017	post regular add wage	493.75	912
28/07/2017	post exceptional add wage	518.4375	957.6
01/07/2018	post regular add wage	593.4375	1032.6
30/06/2019	post encouragement and promotion wage	688.109375	1135.86
01/07/2019	post regular add wage	763.109375	1215.3702
30/06/2020	post encouragement and promotion wage	876.2648438	1336.90722
01/07/2020	post regular add wage	941.2648438	1430.490725

 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 

[v-kelly-msft]

Hi @Islam ,

1. If current row contains a value in RegDecCodeNum in FTransactions2 table then we wanna check the corresponding EmpID value in FTransactions2 table also

By what you said in Step2,column PreviousRegAddDate.you said you wanna check the corresponding EmpID value,how to check it?Be equal to the value which is filtered in RegdecCodeNum?

For column RegAddBaseWage ,you said:

1. 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

What do you mean by the previous value,is it from dtblBasicEmpInfo table?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Islam]

ok sir for the first question you asked about step 2 this one 

1. If current row contains a value in RegDecCodeNum in FTransactions2 table then we wanna check the corresponding EmpID value in FTransactions2 table also

what i meant here is that i wanna once we find a value in RegDecCodeNum to store the corresponding EmpID in current row in a variable so we can use it as a criteria to filter the FTransactions2 table to get only previous rows not next row

For question two about that step

1. 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

i mean the base wage for that particular column that will calculate all wage raises based on is going to be the base wage in dtblBasicEmpInfo Table i mean if there is no previous regular adds for the filtered EmpID then we bring the wage from dtblBasicEmpInfo table or else get the previous calculated wage after adding the previous regular add after taking all criterias in this step in consideration of course

1. 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

you can check the table in the post with the expected answer for each employee and you will get it i think

[v-kelly-msft]

Hi @Islam ,

I do appreciate your patience so far,but it may cost me some more time to figure it out,I will be back to you once I get a result.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Islam]

ok sir take your time i'll be waiting

[v-kelly-msft]

Hi @Islam ,

Sorry for the late reply.

Per your request,I create a measure to get the result of "PreviousRegAddDate",I'm not sure whether I have understood correctly.

If RegDecCodeNum has a value,then check the corresponding EmpID value,here I get a result as below:

Now you need the previous value of each EmpID,which should be as below:

Then get the nearest RegDecCodeNum ID which is 1,and link the ID in dtblRegularAdds to get the DecImpDate,which is 2015/7/1,for the blank rows,use "Decision Apply Date" value instead.

If so,use below dax expression:

Measure = 
var _tab=CALCULATETABLE(VALUES('FTransactions2'[EmpID]),FILTER(ALL(FTransactions2),'FTransactions2'[RegDecCodeNum]<>BLANK()))
var _previousID=CALCULATE(MAX('FTransactions2'[EmpID]),FILTER(ALL('FTransactions2'),'FTransactions2'[EmpID]<MAX('FTransactions2'[EmpID])&&'FTransactions2'[EmpID] in _tab))
var _closestcode=CALCULATE(MIN('FTransactions2'[RegDecCodeNum]),FILTER(ALL('FTransactions2'),'FTransactions2'[RegDecCodeNum]>=_previousID))
Return
IF(ISBLANK(MAX('FTransactions2'[RegDecCodeNum])),BLANK(),
IF(_previousID=BLANK(),MAX('dtblassignmentDec'[Decision Apply Date]),
CALCULATE(MAX('dtblRegularAdds'[DecImpDate]),FILTER('dtblRegularAdds',VALUE('dtblRegularAdds'[RegDecCodeNum])=_closestcode))))

And you will see:

If I miss something,pls correct me.

If it's what you need,let me know,I would provide the dax expression for the other 2 columns.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Islam]

Ok sir I'll check it asap and inform of result 

I'll send you a private message

[PaulDBrown]

@Islam 

Please read and follow the recommendations in this thread:

How to get your question answered quickly 

[Islam]

what i understood from this guiding article is that i have to post a manual answer or solution for my problem so everyone take it as a guidance through his help on this

i attached a pbix file and i can share a simple table showing the manual answer of my question

[v-kelly-msft]

Hi  @Islam ,

Could you pls summarize your requirements by showing the raw data with expected output?It is too complicated...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Islam]

check it now sir i modified the post

[Islam]

unfortunately i tried to simplify what i need as much i could but as a general idea it's a full HR system using Excel power pivot and power query and VBA for excel and could use SSMS

can i have a private voice conversation with you sir to explain further and then you will post what we conclude here in the post as your solution