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

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's avatar
    milanpasschier2
    Icon for Helper I rankHelper I
    4 years ago
    • Hey, probably, will check it tomorrow 😀
  • milanpasschier2's avatar
    milanpasschier2
    Icon for Helper I rankHelper I
    4 years ago

    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'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!