Revised principle amount calculation Urgent

[mojain]

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

[Sahir_Maharaj]

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.

[mojain]

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

        )

    )

RETURN

IF(

    CurrentIndex = 0,

    BaseAmount,

    PreviousAmount * CurrentRate

)