Forum Discussion
Implementing Excel table in Power Bi where a column value depends on another column earlier value
Hi,
Share the source data (not the expected result which you have pasted in the original message) in a format that can be pasted in an MS Excel file.
This is source data is
| Age | Contribution | Investment Growth | Fund Value |
| 30 | 2500 | 50 | 2550 |
Now have to calculate forecasting for further Age yrs i.e. 31,32,33 etc based on calculation that
Growth = ( This year contribution+Earlier year fund value ) * 0.02
Contribution is constant for all years
Fund Value = This year contribution + Earlier year fund value+ This year Inv growth
Below is the detailed table to be implemented
(A) Age (B) Contribution (C) Investment Growth (D) Fund Value 30 1174 B2*0.02 =23.48 B2+C2=1197.48 Data Given 31 1174 (B3+D2)*0.02=47.42 B3+C3+D2=2418.90 Forecasting 32 1174 (B4+D3)*0.02=71.85 B4+C4+D3=3664.76 33 1174 96.77535584 4935.54315 34 1174 122.190863 6231.73401 35 1174 148.1146802 7553.84869 36 1174 174.5569738 8902.40566 37 1174 201.5281133 10277.9338 Its a great solution, just one thing:
Measure for **Contri = SUM(Data[Contribution])** which will add in all contribution in the data but my data has only 1st row contribution in there i.e. for today
Age Contribution 30 1174 like below (from your file table: Data )only 1st row data is present and not for all upcomming years and can't create all years contribution in that table as there are more than 1million individual records for which I want this forecasting
Contribution Date 1174 Tuesday 1 January 2030