Forum Discussion
Revised principle amount calculation Urgent
Hi Community Member,
Tagging amitchandak , Ritaf1983, , Idrissshatila , Daniel29195
I have come up with a problem with creating a calculated column in Power BI for calculation of reviesd amount.
Below is the table with the final amount I want and Its excel calculation in side column.
"
Here is the condition.
for 1st row i.e. index = 0 Then amount column valve w.r.t. index =0.
for 2nd row i.e. index = 1 then Final Amount column valve w.r.t. index =0. * ROI_2 of index = 1
for 3rd row i.e. index = 2 then Final Amount column valve w.r.t. index =1. * ROI_2 of index = 2
for 4th row i.e. index = 3 then Final Amount column valve w.r.t. index =2. * ROI_2 of index = 3
and so on.
"
please help me with DAX or Power Query solution.
| Index | Product | amount | Rate | Final Amount | Excel calculation for Final Amount |
| 0 | A | 19964770.4109589 | 1.00325479452055 | 19964770.4109589 | E2 |
| 1 | A | 19964770.4109589 | 1.01331506849315 | 20230602.6964308 | D3*E2 |
| 2 | A | 19964770.4109589 | 1.01346301369863 | 20502967.5776644 | D4*E3 |
| 3 | A | 19964770.4109589 | 1.01361095890411 | 20782032.6267763 | D5*E4 |
| 4 | A | 19964770.4109589 | 1.01361095890411 | 21064896.0188032 | D6*E5 |
| 5 | A | 19964770.4109589 | 1.01331506849315 | 21345376.5520947 | D7*E6 |
| 6 | A | 19964770.4109589 | 1.01346301369863 | 21632749.649018 | D8*E7 |
| 7 | A | 19964770.4109589 | 1.01361095890411 | 21927192.1154736 | D9*E8 |
| 8 | A | 19964770.4109589 | 1.01361095890411 | 22225642.2262399 | D10*E9 |
| 9 | A | 19964770.4109589 | 1.01331506849315 | 22521578.1747865 | D11*E10 |
| 10 | A | 19964770.4109589 | 1.01346301369863 | 22824786.4902684 | D12*E11 |
| 11 | A | 19964770.4109589 | 1.01361095890411 | 23135453.7211825 | D13*E12 |
| 12 | A | 19964770.4109589 | 1.01361095890411 | 23450349.4310095 | D14*E13 |
| 13 | A | 19964770.4109589 | 1.01346301369863 | 23766061.8066368 | D15*E14 |
| 14 | A | 19964770.4109589 | 1.01346301369863 | 24086024.6223021 | D16*E15 |
| 15 | A | 19964770.4109589 | 1.01361095890411 | 24413858.5135996 | D17*E16 |
| 16 | A | 19964770.4109589 | 1.01361095890411 | 24746154.5385189 | D18*E17 |
| 17 | A | 19964770.4109589 | 1.01331506849315 | 25075651.2811414 | D19*E18 |
| 18 | A | 19964770.4109589 | 1.01346301369863 | 25413245.1178415 | D20*E19 |
| 19 | A | 19964770.4109589 | 1.01361095890411 | 25759143.7527605 | D21*E20 |
| 20 | A | 19964770.4109589 | 1.01361095890411 | 26109750.3997844 | D22*E21 |
| 0 | B | 19964770.4109589 | 1.00325479452055 | 19964770.4109589 | E23 |
| 1 | B | 19964770.4109589 | 1.01331506849315 | 20230602.6964308 | D24*E23 |
| 2 | B | 19964770.4109589 | 1.01346301369863 | 20502967.5776644 | D25*E24 |
| 3 | B | 19964770.4109589 | 1.01361095890411 | 20782032.6267763 | D26*E25 |
| 4 | B | 19964770.4109589 | 1.01361095890411 | 21064896.0188032 | D27*E26 |
| 5 | B | 19964770.4109589 | 1.01331506849315 | 21345376.5520947 | D28*E27 |
| 6 | B | 19964770.4109589 | 1.01346301369863 | 21632749.649018 | D29*E28 |
| 7 | B | 19964770.4109589 | 1.01361095890411 | 21927192.1154736 | D30*E29 |
| 8 | B | 19964770.4109589 | 1.01361095890411 | 22225642.2262399 | D31*E30 |
| 9 | B | 19964770.4109589 | 1.01331506849315 | 22521578.1747865 | D32*E31 |
| 10 | B | 19964770.4109589 | 1.01346301369863 | 22824786.4902684 | D33*E32 |
| 11 | B | 19964770.4109589 | 1.01361095890411 | 23135453.7211825 | D34*E33 |
| 12 | B | 19964770.4109589 | 1.01361095890411 | 23450349.4310095 | D35*E34 |
| 13 | B | 19964770.4109589 | 1.01346301369863 | 23766061.8066368 | D36*E35 |
| 14 | B | 19964770.4109589 | 1.01346301369863 | 24086024.6223021 | D37*E36 |
| 15 | B | 19964770.4109589 | 1.01361095890411 | 24413858.5135996 | D38*E37 |
| 16 | B | 19964770.4109589 | 1.01361095890411 | 24746154.5385189 | D39*E38 |
| 17 | B | 19964770.4109589 | 1.01331506849315 | 25075651.2811414 | D40*E39 |
| 18 | B | 19964770.4109589 | 1.01346301369863 | 25413245.1178415 | D41*E40 |
| 19 | B | 19964770.4109589 | 1.01361095890411 | 25759143.7527605 | D42*E41 |
| 20 | B | 19964770.4109589 | 1.01361095890411 | 26109750.3997844 | D43*E42 |
2 Replies
Hello mojain,
Can you please try the following :
Final Amount = VAR CurrentIndex = YourTable[Index] VAR CurrentRate = YourTable[Rate] VAR BaseAmount = YourTable[amount] VAR PreviousAmount = CALCULATE( MAX(YourTable[Final Amount]), FILTER( YourTable, YourTable[Index] = CurrentIndex - 1 ) ) RETURN IF( CurrentIndex = 0, BaseAmount, PreviousAmount * CurrentRate )Hope this helps.
Hi Sahir_Maharaj ,
Thank you so much for the solution, but in my case I am getting an error as below with DAX.Error :- A circular dependency was detected: Data (5)[Final Amount].
Please help me with the solution.
In my case I have to use the previous value from the same column and it would be ok as well if M query is provided.Final Amount =VAR CurrentIndex = 'Data (5)'[Index]VAR CurrentRate = 'Data (5)'[ROI_2]VAR BaseAmount = 'Data (5)'[Amount]VAR PreviousAmount =CALCULATE(MAX('Data (5)'[Final Amount]),FILTER('Data (5)','Data (5)'[Index] = CurrentIndex - 1))RETURNIF(CurrentIndex = 0,BaseAmount,PreviousAmount * CurrentRate)
