How do I avoid circular dependency when 2 columns require the earlier results of each other

[rwychan9]

Hi all,

I have a circular dependency that I am unable to solve after a week of trying.

There are 2 tables in the attachment:

• TESTbondTransactions lists out transactions detail.
  • Settlement date is the transactions date
  • Book Value is Quantity x Price /100
  • Start/End Range is the range that this transaction is active, i.e. the 'Start Range' is the transactions date of the first purchase, and 'End Range' is the date that it was fully sold off, or the maturity date of the bond. In this case, I filtered the results to only have 1 bond to work with and it has not been completely sold, so end date is the maturity of May 3, 2027.
  • QuantitySold - this will be between 0 and -100%; this denotes what percentage of the position (at the time of sale) was sold.
  • PrevMonthCarryingValue - this looks into the bondSchedule table and find the [Carrying Value] as of the previous month-end.
• TESTbondSchedule is the amortization schedule.
  • MonthlyDates shows the end-of-month from the Start Range to the End Range.
  • Start/End Range comes from the Transactions table.
  • Amortized (SL) is the straight-line amortization taken each month.
  • Book Value comes from the Transactions table, but will change through time based on transactions.

The issue that I have is the Carrying Value. In the bondSchedule, the [Carrying Value] of a bond starts off as the [Book Value] at purchase, then as the month goes on, the monthly [Amortized (SL)] gets added.

When there is a partial sale (i.e. from the bondTransactions table, the sale of 76.78% of position on July 10th, 2023, the gain/loss will be calculated by: [PrevMonthCarryingValue] * [QuantitySold] - [Book Value]. To explain, the gain/loss is comparing the [Carrying Value] against the [Book Value] of the transaction (based on % of position sold).

My issue is once there's a gain/loss, it needs to be carried in the [Carrying Value] going forward. i.e. in the "07-31-2023" row on the bondSchedule, the Carrying Value should be $123268.24 + (-67748.11).

I am getting a circular dependency for obvious reason but I am not sure how to solve this problem. bondSchedule Carrying Value = Book Value + Cumulative Amortization + Gains/Losses, but then to calculate a gain/loss in the bondTransactions table, I need to bring Carrying Value in.

I tried calculating the Carrying Value directly in the gain/loss formula in the bondTransactions table, but then I'll also have to sum all the gain/loss up to that point if there are more than one partial sale, and I couldn't get it to work. I am fairly new to Power Bi so any help would be appreciated.

The example is a simple 1 security, 1 client, 1 partial sale transaction, but the full dataset inclues multiple clients and multiple partial sales. 

Thanks!

Sample Bond Schedule 

[amitchandak]

@rwychan9 , You have to build this using cumulative only. As power bi dax does not support recursive

example like we do Intital Inventory + In -Out

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

Power BI Inventory On Hand
Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

[rwychan9]

Thank you for your suggestion. I will give it a shot.