Calculated column with compounded accumulated performance (for stocks)

[aolivero]

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.

[v-shex-msft]

HI @aolivero,

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

Regards,

Xiaoxin Sheng

[aolivero]

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

[v-shex-msft · Accepted solution]

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