Implementing Excel table in Power Bi where a column value depends on another column earlier value

[Raj12]

I want to implement an excel table in Power Bi
Ideally I have 1st column data i.e. for year 30th and rest all should populate based on calculation:

Year	30	31	32	33	34	35	36	37	38	39	40
Contribution	2500	2500	2500	2500	2500	2500	2500	2500	2700	2500	2500
Growth	50	101	153.02	206.0804	260.202008	315.406	371.7142	429.1485	491.7314	551.566	612.5974
Fund Value	2550	5151	7804.02	10510.1	13270.30241	16085.71	18957.42	21886.57	25078.3	28129.87	31242.47

Growth = ( This year contribution+Earlier year fund value ) * 0.02
Contribution is constant for all years

Fund Value = This contribution + Earlier year fund value+ This year Inv growth

I tried creating calculated column but it gives error of circular dependencies and tried creating functions in Power query but still no luck. any help is appreciated

Thank you

[ronrsnfld]

Using Power Query M Code, it is possible to create the table you show from the source data you supply:

• In this code, the number of years is hard coded
• The growth rate is calculated from the source data
• List.Generate is used to generate the required columns
• I assumed the $2700 contribution in year 38 was a typo

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZQ0lEyMjUAUaYQNpCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Age = _t, Contribution = _t, #"Investment Growth" = _t, #"Fund Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", Int64.Type}, 
        {"Contribution", Currency.Type}, {"Investment Growth", Int64.Type}, {"Fund Value", Currency.Type}}),
 
    //calculate new columns
    yrs = 11,
    rate= #"Changed Type"[Investment Growth]{0}/#"Changed Type"[Contribution]{0},
    
    newTblCols = List.Generate(
        ()=>[yr=#"Changed Type"[Age]{0},
             contr=#"Changed Type"[Contribution]{0},
             gr=#"Changed Type"[Investment Growth]{0},
             fv=#"Changed Type"[Fund Value]{0}, 
             idx=0],
        each [idx] < yrs,
        each [yr=[yr]+1,
              contr=[contr],
              gr = ([fv] + [contr]) * rate,
              fv = [fv] + [contr] + ([fv] + [contr]) * rate,
              idx = [idx]+1],
        each {[yr],[contr],[gr],[fv]}
    ),

//create table from columns and prepend with a column for the Row labels
    #"New Table" = Table.FromColumns({{"Year","Contribution","Growth","Fund Value"}} & newTblCols),

//Promote first row to the column Headers
    #"Promoted Headers" = Table.PromoteHeaders(#"New Table", [PromoteAllScalars=true]),

//Set the data types for new table
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year", type text}} & 
        List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Promoted Headers"),1), each {_, Currency.Type}))
in
    #"Changed Type1"

But, as you show in your example, this is three rows of results for a single entry row.

How would you want to display the results when you have multiple entry rows?

[Ashish_Mathur]

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.

[Raj12]

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

[Raj12]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Raj12]

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

[Ashish_Mathur]

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.

[Raj12]

Thank you for your response.

Can't we take **Contri** measure as given value for each unique record and use it for calculating forecasting for other subsequent years as for other years **Contri* will remain same.

[Ashish_Mathur]

I do not know of a way to do that.

[Raj12]

Okay and how can we get sum of a measure in there like if I take Fund value in a card visual then it shows only the last value not the sum of all the rows for fund value  as shown below

[Ashish_Mathur]

Write this measure

Measure = if(HASONEVALUE('Calendar'[Year]),[Fund value],SUMX(VALUES('Calendar'[Year]),[Fund value]))

[Raj12]

Thank you so much for the help .

[Ashish_Mathur]

You are welcome.

[Raj12]

Jus one small thing.
I am trying to add in some initial fund value value in first year fund value only and subsequent calculation is as it is. Is it spossible?

[Raj12]

Tried to update existing meausre of Fund Value as:
Fund Value__ =
var val = [Inv till date__]+[Inv grwoth till date__]
return
if([Years elapsed]=1,val+SUM('Data'[Inital Fund]),val)

but it just adds in initial fund column value in 1st row but subsequent rows calulcation is not taking that added in value