How to calculuate balance run off measure?

[adin]

Hi guys,

I need to create a measure that uses a deposit balance and then applying a run-off rate that I get from a reference table I need to calculate what the balance will be in subsequent periods. For example, I need to calculate what the balance will be each month from the first month and then also how many months does it take to get to zero balance.

This is the result I'm after

I can not seem to do this without creating a circular reference error in Dax, this is because I'm using the previous period balance to calculate the current period balance.

Any help would be appreciated

By the way, I know that the NPV and xNPV formula are generally idea for doing this but I can not use them as once i have figured how to do this, I will have to apply extra logic that is custom to my use case. Hence why I'm trying to create the calcs. from first principles. For example, my uses case needs me to factor in a changing runoff rate, whereas the NPV formula assumes a constant runoff rate etc... 

cheers

[adin]

hi 

can i get help with the above measure and in particular getting the total to sum up to the correct amount of the column?

[adin]

@amitchandak is this possible in power bi measures, or am I wasting time with measures and need to do it in columns or in my DB

[v-kkf-msft · Accepted solution]

Hi @adin ，

You can use the PRODUCTX function, like this:

Measure = 
VAR __d = MAX ( 'runoffrate'[Period] ) 
RETURN 1000 * CALCULATE ( PRODUCTX ( runoffrate, [Rate] ), ALLSELECTED ( runoffrate ), runoffrate[Period] <= __d )

Rate = 1 - MAX(runoffrate[Run off rate])

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.