Calculate a measure based on previous row in PowerBi Dax

I am creating some what-if analysis in Power BI, which involves several created parameters, allowing the users to alter inputs in order to create graphs and financial estimates / outputs.

I would like to achieve the following output in DAX, where Net Incoming is a result of selected parameters, Gains 1 and Gains 2 are in month calculations based on the previous end of month results against some flat rate, and the end of current month is the summation of these.

Months	Net Incoming	Gains 1	Gains 2	Start_of_month	End_of_month
0	25,000	0	0	-	25,000
1	240	86.46	10.42	25,000	25,337
2	252	87.62	10.56	25,337	25,687
3	268	88.83	10.70	25,687	26,055
4	235	90.11	10.86	26,055	26,391
5	237	91.27	11.00	26,391	26,730
6	253	92.44	11.14	26,730	27,086
7	223	93.67	11.29	27,086	27,414
8	298	94.81	11.42	27,414	27,819
9	212	96.21	11.59	27,819	28,138
10	263	97.31	11.72	28,138	28,510
11	262	98.60	11.88	28,510	28,883
12	223	99.89	12.03	28,883	29,218

Sample Data

Hence this is a recursive function which can be summarised as

N_end_of_month  =   (Previous End_of_month) + (Previous End_of_month * (Base_Rate / 12) ) + (Previous End_of_month * (Bonus_Rate / 12) ) + Net Incoming

I've seen some examples of creating recursive functions in Power Query - however I don't believe I can pull the parameters used in the dashboard through in order to use them. Perhaps there is some way to solve this function for N and hence not require recursion? Otherwise how can I introduce this simple recursive example into DAX?

Solution

Here is an example of how the above can be achieved with iterative functions to calculate compound interest with addition net coming in, with two rates.

Set-up
Initial table for example called WhatIf:

Months	Net Incoming
0	25,000
1	240
2	252
3	268
4	235
5	237
6	253
7	223
8	298
9	212
10	263
11	262
12	223

Two rates defined in measures (these can come from anywhere):

Rate Base = 0.0415

Rate Bonus = 0.005

New measures needed to produce the desired table in the question

This is the main measure, it works by using compound interest formula per month net incoming, with both rates added together.

End of Month = 
  var rate = DIVIDE([Rate Base] + [Rate Bonus], 12)
  var thisMonth = MAX(WhatIf[Months])
  var prevMonths = FILTER(ALL(WhatIf[Months]), WhatIf[Months] < thisMonth )
  var prevNet = ADDCOLUMNS(
    prevMonths,
    "netAmt", CALCULATE(SUM(WhatIf[Net Incoming]))
  )
  var result = 
    SUMX(
      prevNet,
        (
          var netMonth = thisMonth - 'WhatIf'[Months]
          var netAmt = [netAmt]
          return netAmt * (1 + rate)^netMonth
        )
    )

  return result + MIN(WhatIf[Net Incoming])

With the above main measure, the rest are fairly simple:

Start of Month = 
  var thisMonth = MAX(WhatIf[Months])
  return [End of Month](WhatIf[Months] = thisMonth - 1)


Gains 1 = 
  var gain = [End of Month] - [Start of Month] - MIN(WhatIf[Net Incoming])
  var gainRatio = DIVIDE([Rate Base], [Rate Base] + [Rate Bonus])
  return gain * gainRatio


Gains 2 = 
  var gain = [End of Month] - [Start of Month] - MIN(WhatIf[Net Incoming])
  var gainRatio = DIVIDE([Rate Bonus], [Rate Base] + [Rate Bonus])
  return gain * gainRatio

Result