Forum Discussion
Recursive Function Forecasting
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
10 Replies
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.
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.
or use dax formula like this
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 tablewdx223_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 tableAlso 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?
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 tablehi 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.
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}
