Need Help Calculating Book Value (Adjusted Cost Base) of Investments

[NKrichmond]

Hi there. I am modernizing some Excel databases and am having some problems with DAX. I need a calculated column or a measure that will calculate the change in book value (adjusted cost base) for each transaction and one that will calculate the cumulative ending book value for a given account #. I have attached a link to a sample spreadsheet.

https://docs.google.com/spreadsheets/d/13DKQaMC55h0yg_wu-kDnNrI2-AzpyOK4/edit?usp=sharing&ouid=10070... 

I will try to explain my issue as clearly as possible:

- There are four types of transactions: Contribution, Redemption, Distribution - Reinvested, and Distribution - Paid Out.

- The change in book value for a "contribution" or "distribution - reinvested" is equal to the market value of the transaction. The change in book value of a "distribution - paid out" is 0. The change in book value for a "redemption" is equal to the number of units sold multiplied by the previous book value per unit. Previous book value per unit is simply the ending total book value divided by number of units owned on the last transaction date. This is where I am running into problems as I am getting circular reference errors trying to calculate the change in book value, where it depends on the sum of previous changes in book value.  

- There are three example accounts in this workbook and their book value calculation are independant of each other. 

The calculation is fairly straightforward in Excel but depends on the data being sorted properly. It uses an account instance formula to look up the previous transaction and grab the book value per unit. 

Any help with this would be much appreciated, Thanks! 

[milanpasschier2]

Hey man,

Try:

Prev. BVPS = 

VAR _ACCOUNT = 'Table'[Account #]
VAR _ACCOUNT_INSTANCE = 'Table'[Account Instance]

VAR _PREV_ENDING_BVPS = MAXX(
    CALCULATETABLE('Table', FILTER('Table','Table'[Account #]=_ACCOUNT&&'Table'[Account Instance]<_ACCOUNT_INSTANCE)), 'Table'[Ending BVPS])

RETURN _PREV_ENDING_BVPS

Please mark as solution if it fits your needs.

Best,

Milan

[NKrichmond]

Thanks Milan, that formula works well and solves the account instance issue I was having. However, the main issue I am having remians: the book value is not calculating properly and I am getting a circular dependancy error whenever I try to calculate it. This is because the book value calculation for redemptions depends on the previous BVPS, which depends on earlier ending book value calculations. Do you know if there is any way to get around this?

[milanpasschier2]

Hey,

I think I have fixed everything!

I have made some minor changes to the formulas from @v-easonf-msft and I included an indexed column, see also https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

I have created a calculated column Test with no issues with your formula:

=IF([Action] = "Redemption" ,[Change in Units]*[Prev. BVPS],0)

You can download the new pbix here: https://milanpasschier2.s3.eu-central-1.amazonaws.com/Com+2.pbix

Please let me know your thoughts and mark my post as solution if it fits your needs.

Best,

Milan

[milanpasschier2]

Hey,

I have found some issues in the Prev. BVPS calculated column, please change to:

Prev. BVPS = CALCULATE(SUM('Table'[Ending BVPS]),FILTER('Table','Table'[Account #]=EARLIER('Table'[Account #])&&'Table'[Account Instance]=EARLIER('Table'[Account Instance])-1))

You can download the UPDATED file here: https://milanpasschier2.s3.eu-central-1.amazonaws.com/Com+2.1.pbix

Best,

Milan

[NKrichmond]

Thanks for the work, that is an improvement and is helpful. However, the ending book value calcualtion is still wrong. The way you have it set up, it is summing the market value for all "Actions". The change in book value should equal market value for a "Contribution" and "Dstr'n - Reinvested", 0 for "Dstr'n - Paid out", and the change in units * the Prev. BVPS for a "Redemption".

The change in book value for a given transaction would be calculated in a new column as below:  

Change in BV = IF(OR('Table'[Action] = "Contribution",'Table'[Action] = "Dstr'n - Reinvested"), 'Table'[Market Value],'Table'[Change in Units]*'Table'[Prev. BVPS])

Ending Book Value could then be: 

Ending Book Value = 

VAR _ACCOUNT = 'Table'[Account #]

VAR _INDEX = 'Table'[Index]

RETURN

CALCULATE(SUM('Table'[Change in BV]),FILTER('Table','Table'[Account #]=_ACCOUNT && 'Table'[Index]<=_INDEX))

Ending BVPS and Previous BVPS would be the same as you have them. 

This will return a circular dependancy error as the Prev. BVPS is dependant on the sum of the changes, which is dependant on the Prev. BVPS. This is the issue I'm trying to get around. Any ideas on this? Thanks!

[milanpasschier2]

Hey, is it okay to calculate this as a measure?

[NKrichmond]

Yep, whichever way that gets the job done is fine!

[milanpasschier2]

Hey, Okay! Check the visual view: https://drive.google.com/file/d/1KGcNYTAWpG8MS5TPOqIjJ8wVykjooS3a/view?usp=sharing

[NKrichmond]

The measures you added work well, but the Prev. BVPS and Ending BVPS still use the old values from the calculated columns and return the incorrect values. When I try to update them to use the new measures I again get a circular dependancy error. 

[milanpasschier2]

Hmmm, I'm stuck too... @tamerj1 Any thoughts on this one?

[tamerj1]

@milanpasschier2 
First of all please note that this report table is summarized by date. Therefore, some rows represents multiple records from the source table (multiple records in the same date). What does the acount instance (for example) mean in this case? Other questions will come up as I move forward with the file.

[NKrichmond]

That is correct, there are multiple accounts that may have transactions on the same date and one account may also have multiple transactions on the same date. The account instance is just a count of transactions in one account up until that point. It is important as we need to find the most recent (current instance -1) book value per share and the transactions are ordered (in my main database) so that distributions occur first, then redemptions, and lastly contributions to properly calculate book value.    

[tamerj1]

Hi @NKrichmond 

Actually @milanpasschier2  has done a very great job. The thing which I don't understand is the meaning of each measure. Not sure if that was illustrated somewhere in the post as I didn't read it all. If not please provide detailed description of each measure. Thank you

[NKrichmond]

Yes it is close, but still not calculating properly. Ill try to explain them all:

- Account Instance: The number of transactions in an account up until the current date.

- Change in units: The number of units bought, sold, or distributed in each transaction. = (Market Value / Price) for Contributions, Redemptipons and Dstr'n - Reinvested. = 0 for Dstr'n - Paid out. This measure is working well.

- Ending Units: The number of units an account holds on that specific date. = cumulative sum of the change in units for a specific account up until that date. This measure is working well.

Now this is where the problems start...

- Change in Book Value: The change in book value due to a transaction. = Market Value for Contributions and Dstr'n - Reinvested. = 0 for Dstr'n - Paid Out. = (Change in Units * Prev. BVPS). for Redemptions. This measure is set up correctly.

- Ending book value: The book value of an account on that date. Calculated the same way as ending units but is instead a cumulative sum of Change in Book Value. The "Ending Book Value Measure" is set up correctly. Note that the "Ending Book Value" calculated column is not calculating correctly.   

- Ending BVPS (book value per share): = (Ending Book Value / Ending Units)

- Prev. BVPS: = Ending BVPS from the most recent transaction

The problem is that the Change in Book Value relies on the Prev. BVPS, which relies on the Ending Book Value, which relies on the Change in Book Value, resulting in a circular dependancy. 

[tamerj1]

@NKrichmond 

Ok this is a recursive calculations which dax don't support. The workaround if possible is usually complex. I'll do my best to find a solution. I'll let you know if I was able to reach any solution. Good night for now. 

[milanpasschier2]

• Hey, probably, will check it tomorrow 😀

[v-easonf-msft]

Hi, @NKrichmond 

Please try calculated columns as below:

Account Instance = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Account #]=EARLIER('Table'[Account #])&&'Table'[Date]<=EARLIER('Table'[Date])))

Change in Units = IF('Table'[Action]="Dstr'n - Paid out",0,'Table'[Market Value]/'Table'[Price])

Ending Units = CALCULATE(SUM('Table'[Change in Units]),FILTER('Table','Table'[Account #]=EARLIER('Table'[Account #])&&'Table'[Account Instance]<=EARLIER('Table'[Account Instance])))

Ending Book Value = CALCULATE(SUM('Table'[Market Value]),FILTER('Table','Table'[Account #]=EARLIER('Table'[Account #])&&'Table'[Account Instance]<=EARLIER('Table'[Account Instance])))

Ending BVPS = IFERROR( 'Table'[Ending Book Value]/'Table'[Ending Units],0)

Prev. BVPS = CALCULATE(SUM('Table'[Ending BVPS]),FILTER('Table','Table'[Account #]=EARLIER('Table'[Account #])&&'Table'[Account Instance]=EARLIER('Table'[Account Instance])-1))

result:

Best Regards,
Community Support Team _ Eason

[NKrichmond]

Thanks for the reply. There are a few issues with that solution.

- Book value is being calculated as the sum of market values by account, where it needs to be calculated based on Action (transaction type). If i add "&& [Action] <> "Dstr'n - Paid out"" to the filter then it ignores paid out distributions as it should. This formula then works correctly for everything except redemptions, which is where the issues come from. 

- If i add a new column for change in book value from redemptions, calculated as =IF([Action] = "Redemption" ,[Change in Units]*[Prev. BVPS],0) and then try to add this to the Ending Book Value calculation I get a circular dependancy error.

-  The account instance formula is almost what I was looking for. The issue is that there can be multiple transactions on the same day and the instance will sum both and return a value 2 higher than the previous. The Prev. BVPS column then tries to look up that value -1 and returns nothing:

Thanks again for the help, any other info would be appreciated although I'm starting to wonder if what I am looking for is even possible within DAX or if should stick with Excel...