Forum Discussion
Resolver IRecursive calculation
Following from other replies, you basically need to calculate the cumulative product of your 'growth factors'.
Gerhard Brueckl's blog (link above) had a method using summing logarithms, then mentioned that you can now use PRODUCTX.
Here are two examples using PRODUCTX in a calculated column. You could adapt this to a measure if that makes sense as well.
Resolver IThanks v-qiuyu-msft.
Basically what I am trying to do is like compund interest.
Imagine you have a 100 dollars. Each day you get 1% on your 100 so in day 2 you have 101, In day 3 you have 102.01, In day 4 you have 103.0301 etc.
Now, let's change it a bit - 1% a day is not fixed. It can change. On one day it's 1%, the next day it's zero, the next day it's 2%.
You need to be able to calculate your return over time. After 3 or 300 or 4562 days, for any given date range.
Example of dataset :
date,interest
11/1/2016, 0.01
11/2/2016, 0.01
11/3/2016, 0.005
11/4/2016, 0
11/5/2016, 0.02
So, based on the above dataset, if I want to calculate the return for the entire period, it would be like that :
100*(1+0.01)*(1+0.01)*(1+0.005)*(1+0)*(1+0.02)
If I want to calculate the return in the date ramge 2/11-4/11 :
100*(1+0.01)*(1+0.005)*(1+0)
I hope it makes sense.
Please let me know if you need additional information.
Any help is much appreciated. Thanks !
Super User
Following from other replies, you basically need to calculate the cumulative product of your 'growth factors'.
Gerhard Brueckl's blog (link above) had a method using summing logarithms, then mentioned that you can now use PRODUCTX.
Here are two examples using PRODUCTX in a calculated column. You could adapt this to a measure if that makes sense as well.
- zivhimmel
Hi OwenAuger, thanks allot for taking the time and answering.
I don't see however how it solves the compound interest problem.
I'm missing the part where you multiply the day's Growth Factor with yesterday's.
If we had 1 dollar and the growth factor is 1%, then we now have 1.01.
The next day, if the growth factor is again 1%, then we now have 1.021.
I need a way to calculate it(column C below) :
Thanks !
askeltonSorry this is quite an old post, however I saw you were online and this applies to what I am working on now. I'm fairly new to PowerBI and you said this could be adapted to a measure if needed. Would you mind explaining how? When I try I get
"A single value for column 'Date' in table 'Data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
This is my overall problem i'm working on.