Forum Discussion

mojain's avatar
mojain
Frequent Visitor
2 years ago

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 amo...
Need Help
Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
2 years ago

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's avatar
    mojain
    Frequent Visitor
    2 years ago

    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
    )