Forum Discussion
Need Help Calculating Book Value (Adjusted Cost Base) of Investments
Helper IHey 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
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, probably, will check it tomorrow 😀
- 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
