Forum Discussion

zivhimmel's avatar
zivhimmel
Icon for Resolver I rankResolver I
9 years ago
Solved

Recursive calculation

Hi, How can I do the following ? First day - Column A = 100 2nd day and every day that follows, column A=yesterday's value of column A X column B.   Thanks !    
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    zivhimmel

     

    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.

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

Hi zivhimmel,

 

Would you please share some sample data and desired results so that we can try to test it?

 

Best Regards,
Qiuyun Yu

  • zivhimmel's avatar
    zivhimmel
    Icon for Resolver I rankResolver I
    9 years ago

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

     

    • OwenAuger's avatar
      OwenAuger
      Icon for Super User rankSuper User
      9 years ago

      zivhimmel

       

      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's avatar
        zivhimmel
        Icon for Resolver I rankResolver I
        9 years ago

        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 !