Recursive Function Forecasting

[avachris]

Hi there - 

I have a list of portfolios that I'd like to forecast through time. 

I also have a seperate table which outlines the quarter over quarter growth rate.

I think the right solution here is a recursive function. 

Here are the tables below

Input - Portfolio Table initial values to apply forecast

Portfolio	Starting Value
Portfolio A	100
Portfolio B	150

Input - Forecast table - contains the forecast assumptions

Scenario	Modeled Quarterly Growth Rate (%)	Additional Growth ($)	Quarter Index
Good	10	0	1
Good	10	50	2
Bad	1	0	1
Bad	2	0	2

Output- The function being:

Forecasted Value = Prior Value x Quarter over Quarter Growth + Additional Growth

Portfolio	Scenario	Modeled Quarterly Growth Rate (%)	Additional Growth ($)	Quarter Index	Forecasted Value
Portfolio A	Good	10	0	1	110
Portfolio A	Good	10	50	2	171
Portfolio A	Bad	1	0	1	101
Portfolio A	Bad	2	0	2	103.02

I was playing with a recursive function in M going something like this: 

(totalLoops as number, loop as number, Value as number, ForecastTable as table ) =>
let
CurrentValue = Value * ForecastTable{3}QoQ +ForecastTable{4}AdditionalGrowth , \\ applies the growth rate and additional growth
CurrentLoop = loop +1, \\ loop counter
ForecastTable =

\\ I am having trouble with this part
if ForecastTable is null then Table.FromRecords({[Scenario = "a", QoQ = 0, Quarter = 0,ForecastValue = Value]}) else 
Table.Combine({ForecastTable,Table.FromRecords({[Scenario = "b", QoQ = 0, Quarter = 0]})}),
Output =
if CurrentLoop >= totalLoops
then ForecastTable
else @fxForecast (totalLoops,CurrentLoop,CurrentValue,ForecastTable)
in
Output

[wdx223_Daniel]

assume the maturity date is in the 3rd column of portfolio table, then try update your code as below.

let
    PT = Table.ToRows(Portfolio),
    FT = Table.ToRows(Forecast)

in #table ({"Porfolio","Maturity Date"}&Table.ColumnNames(Forecast)&{"Forecast Value"},

List.TransformMany(PT, each FT, 


(x,y)=> {x{0},x{2}}&y& // starts building the table with the first column of portfolio and column from forecast

{List.Accumulate // start the accumulate loop

(List.Select(FT,each _{3}<=y{3} and _{0}=y{0} // filters a table for where the scenario names match and where the forecast rows are lessthan or equal to current forecast period


),x{1}, // seeds the value with the jump off of the portfolio


(x,y) => x*(1+y{1}/100)+y{2})})) // applies the assumptions from the forecast table

[avachris]

hi there - I want to be able to leverage the portfolio date within the list.accumulate loop. Something like if the maturity date is less than the forecasted date then the forecasted value is 0.  This is a much more simple example than what I am really trying to do.  Definitely seeing that the Dax solution appears to be way more flexible. 

[wdx223_Daniel]

then change this part of the code:            List.Select(FT,each _{3}<=y{3} and _{0}=y{0} and x{index of date column}<=y{index of date column}

[wdx223_Daniel]

then change this part of the code:            List.Select(FT,each _{3}<=y{3} and _{0}=y{0} and x{index of date column}<=y{index of date column}

[avachris]

Hi there - For folks looking at this, here is the commented code as far as I can figure it out.

let
    PT = Table.ToRows(Portfolio),
    FT = Table.ToRows(Forecast)

in #table ({"Porfolio"}&Table.ColumnNames(Forecast)&{"Forecast Value"},

List.TransformMany(PT, each FT, 


(x,y)=> {x{0}}&y& // starts building the table with the first column of portfolio and column from forecast

{List.Accumulate // start the accumulate loop

(List.Select(FT,each _{3}<=y{3} and _{0}=y{0} // filters a table for where the scenario names match and where the forecast rows are lessthan or equal to current forecast period


),x{1}, // seeds the value with the jump off of the portfolio


(x,y) => x*(1+y{1}/100)+y{2})})) // applies the assumptions from the forecast table

 @wdx223_Daniel a couple of questions for you: 

In the following step is there a way to access the original Portfolio table to get additional columns, for example it would be helpful to grab the portfolio maturity date for the portfolio table. If not, was thinking I could use your method to create a permutation of the forecast table and portfolio table (essentially making the Y table bigger with all relevant columns) 

(x,y) => x*(1+y{1}/100)+y{2})})) // applies the assumptions from the forecast table

Also is the list accumulate limited to only 1 value, what if I wanted to track two values through time so that I could then compare those two values in a future period? 

[wdx223_Daniel]

or use dax formula like this

[wdx223_Daniel · Accepted solution]

[Syndicate_Admin]

Wow that's really incredible - I enjoy and appreciate the DAX solution as well. 

Which in your opinion performs better? This is a simple example of a calculation that I'll have to run and then load to an OLAP cube via excel add in. Was thinking Power Query as it "generates" the data set, vs DAX where its being calculated.

[avachris]

Wow that's really incredible - I enjoy and appreciate the DAX solution as well. 

Which in your opinion performs better? This is a simple example of a calculation that I'll have to run and then load to an OLAP cube via excel add in. Was thinking Power Query as it "generates" the data set, vs DAX where its being calculated.

[Anonymous]

You don't state what trouble you are having, but you might want to change it from

if ForecastTable is null

to

if Table.IsEmpty(ForecastTable) then...

--Nate