Forum Discussion

aolivero's avatar
aolivero
Regular Visitor
8 years ago
Solved

Calculated column with compounded accumulated performance (for stocks)

Hello everyone,

 

I've been reading a lot regarding accumulated sums in a column, but none of the questions/answers used a case like this, where it's not simply adding numbers, but more related to compounding, so here's the problem:


I've got performances by month of a stock, and I want to calculate the accumulated performance during a year, month by month.

An example of the calculation would be:

Accumulated performance in January = 5%

February's performance = 2.5%

 

Accumulated performance on the first of March = (1 + 0.05) * (1+ 0.025)-1 = 7.625% (NOT 7.5% as it would usually be)

 

And here's how I calculate it on excel (last column): 

 

 

Well, that's all I've got, if you need anything else please let me know, I've been trying this for days.

 

Thank you so much in advance.

  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi aolivero,

     

    You can achieve these in excel side and import table with result to power bi.

     

    Maybe you can take a look at below blog to use EXP and LN function to achieve similar effect.(not real recursive calculate)

    Recursive Calculations in PowerPivot using DAX

     

    In addition, you can also try to create a R visual to use r script to achieve recursive calculation.

    R Recursive Function

     

    Regards,

    Xiaoxin Sheng

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    HI aolivero,

     

    Actually, dax can achieve rolling calculate, but recursive calculate is not support.

     

    Regards,

    Xiaoxin Sheng

    • aolivero's avatar
      aolivero
      Regular Visitor

      So...  what I'm asking is not possible?

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi aolivero,

         

        You can achieve these in excel side and import table with result to power bi.

         

        Maybe you can take a look at below blog to use EXP and LN function to achieve similar effect.(not real recursive calculate)

        Recursive Calculations in PowerPivot using DAX

         

        In addition, you can also try to create a R visual to use r script to achieve recursive calculation.

        R Recursive Function

         

        Regards,

        Xiaoxin Sheng