Modelling future cashflows using GENERATESERIES

[adin]

I'm trying to convert an old financial model that was built in excel (using macros and hence running slow) into power bi.

I'm trying to model future cash flows for each loan in my dataset but am struggling to figure how to use generate series along with calculations (in measure) in order to execute each calc per loan per future period. the ultimate end result is i want to be able to slice and dice the numbers not just have one aggregate total 

I managed to work out the logic to calculate future loan value based on runoff rate I look up in a reference table, but now am stuck because the formula only works for one loan (i.e. only when i filter one loan at a time) . the formula for the projected future loan value is below along with a screenshot of the output filtered for only one loan. When I remove the filter the measure breaks.

the result I'm after is the total value of all loans at each future period. 

FuturePeriods = GENERATESERIES(0,132,1)

RunOffRate = 

var FuturePeriodsTable = CROSSJOIN(NetPresentValueData,FuturePeriods)

Var Loan_duration = SUMX(FuturePeriodsTable,NetPresentValueData[Tenure_months] + FuturePeriods[FuturePeriod])

Var DurationCutOffLookup = CALCULATE(max(RunOffRates[DurationBand CutOff]),RunOffRates[DurationBand CutOff]<=Loan_duration)

Return

if(SELECTEDVALUE(FuturePeriods[FuturePeriod]) = 0, 0,

SWITCH(SELECTEDVALUE(NetPresentValueData[GroupId]),

"Group1", LOOKUPVALUE(RunOffRates[group1],RunOffRates[DurationBand CutOff],DurationCutOffLookup)/12,

"Group2", LOOKUPVALUE(RunOffRates[group2],RunOffRates[DurationBand CutOff],DurationCutOffLookup)/12

 ))

Projected Balance = 

VAR OriginalBalance = MAX(NetPresentValueData[Total_current_balance])

VAR __d = MAX ( FuturePeriods[FuturePeriod] )

RETURN

OriginalBalance * CALCULATE ( PRODUCTX(FuturePeriods, (1 - 'Measures Table'[RunOffRate]) ), ALLSELECTED (FuturePeriods), FuturePeriods[FuturePeriod] <= __d )

Having difficulty working with iterator functions, which is what I'm assuming this needs 

Thanks for any help you can provide

[v-jayw-msft]

Hi @adin ,

Have difficult to follow. Please take a look at the topic about How-to-Get-Your-Question-Answered-Quickly .

Also please share the logic of your formula and which SELECTEDVALUE function is you mentioned above.

Best Regards,

Jay

[adin]

i think the error is when i use the SELECTEDVALUE function, but I can not figure how to do it without it