Forum Discussion
Implementing Excel table in Power Bi where a column value depends on another column earlier value
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 Hi,
I know of a way to explode that 1 row into 8 rows but that would mean giving rise to 8 million rows from your original dataset of 1 million rows. I dont think my solution will work efficiently there. Sorry but i cannot help here.