Forum Discussion

NKrichmond's avatar
NKrichmond
Frequent Visitor
4 years ago

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

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 eac...
milanpasschier2's avatar
milanpasschier2
Icon for Helper I rankHelper I
4 years ago

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's avatar
NKrichmond
Frequent Visitor
4 years ago

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!