Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Amortisation table (circular reference)

I am trying to create a amortisation table in powerBI but am stuck as circular references keep occuring.

 

A typical amortisation table has the opening present value, a repayment amount (fixed), an interest amount (variable)

Added together it becomes the closing present value.

 

However, I am currently stuck as my opening present value by nature includes the accumulation of all the interest amounts in previous periods. As a result, I cannot create an interest column (formula = opening present value * 0.01%), since it creates a circular reference. 

 

has anyone encountered this before?

Regards,

 
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You need to think of measures like inventory.using initial or first period build up again

     

    Inventory / OnHand  =
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=max(date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=max(date[date])))

     

    if this closing then we use  min for opening

     

    Inventory / OnHand  =
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <min(date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <min(date[date])))

    // (month wise display)

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Could you provide a sample pbix after removing sensitive data so that we can better understand your scenario.