Forum Discussion
Solved
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 soluti...
wdx223_Daniel
Community Champion
Community Championassume 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
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}