Amortisation table (circular reference)

[Anonymous]

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,

[V-lianl-msft]

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

[amitchandak]

@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)