Calculate cumulative loan balance due

[SaibAnalytics]

Hello guys, 

I need help in coming up with a dax code for calculating cumulative oustanding loan amount per loan ID. I have monthly expected amount and first and last installment expected date. In the first row, i know the loan ref should have paid 135*2, Therefore oustanding due is 1500 - (135*2) . 

@amitchandak , @v-joesh-msft , @Greg_Deckler or Anyone who can help, i will really appreciate this!!!

Loan Reference	Loan Amount	Installment Amount	Number of Installments	Allocation Data	First Inst. Date	Last Inst. Date	Loan Installment days	Cumulative Outstanding
Abx	$1,500.00	$135.00	12	12/4/2022	20/05/2022	20/04/2023	30	=1500 - (135*2)
xyz	$50,000.00	$1,805.56	36	27/08/2020	30/09/2020	31/08/2023	30	As at today or Any date filtered
yds	$1,500.00	$270.00	6	25/11/2021	30/12/2021	30/11/2022	60

[SaibAnalytics]

Hi @daXtreme, Again, thanks alot!

I have used the Dax, it does not have an error but i am unable to see the results when I put the measure on a visual. i get the error, "

Error Message: MdxScript(Model) (110, 38) Calculation error in measure 'Dax Measures'[Outstanding]: A table of multiple values was supplied where a single value was expected.
I should also mention, I have a calender table that has an active relationship with allocation date and an incative relationship withe [First Inst. Date] and [Last Inst. Date]. I tried editing the code as below.

Outstanding =

// Assumptions:

// 1. Dates (your calendar) is not connected to the Loan fact table.

// 2. After the last installment, the amount outstanding is always 0.

// 3. Loan installment days can only be multiples of 30, meaning 1, 2, 3, 4... months.

// If the logic is different, you'll have to adjust the code.

var LastDateSelected = MAX( calendarTable[Date])

var firstInstallmentDate = CALCULATE(All(Loans[First Inst. Date]),USERELATIONSHIP(Loans[First Inst. Date],calendarTable[Date]))

var lastInstallmentDate = CALCULATE(All(Loans[Last Inst. Date]),USERELATIONSHIP(Loans[Last Inst. Date],calendarTable[Date]))

return

SUMX(

Filter(

Loans,

// Other loans will for sure have their outstanding

// balance of 0, so only these satisfying the below

// condition are of interest.

and(

firstInstallmentDate <= LastDateSelected,

LastDateSelected <= lastInstallmentDate

)

),

// For each loan we iterate, we need to figure out

// how many installments have already been paid.

// Bear in mind that if LastDateSelected is a day

// on which an installment should be paid, the installment

// is treated as having been paid.

var FirstInstallmentDay = firstInstallmentDate

var FirstInstallmentDayInCalendar =

CALCULATETABLE(

DISTINCT( calendarTable[Date]),

calendarTable[Date] = FirstInstallmentDay

)

var NumOfInstallments = Loans[Number Installment ]

// Granularity tells you the number of months between

// consecutive payments.

var Granularity = int(Loans[Loan Installment Days] / 30 )

var AllPaymentDays =

GENERATE(

// -1 because we start from 0

GENERATESERIES(0, NumOfinstallments - 1, 1),

var InstallmentNumber = [Value]

var PaymentDay =

DATEADD(

FirstInstallmentDayInCalendar,

Granularity * InstallmentNumber,

MONTH

)

return

ROW( "@PaymentDay", PaymentDay )

)

var PaymentDaysBeforeOrOnLastDateSelected =

FILTER(

AllPaymentDays,

[@PaymentDay] <= LastDateSelected

)

var NumOfInstallmentsPaid =

COUNTROWS( PaymentDaysBeforeOrOnLastDateSelected )

var InstallmentAmount = Loans[Installment Amount]

var AmountPaid = InstallmentAmount * NumOfInstallmentsPaid

var LoanAmount = Loans[Price of Loan]

// Using MAX just in case there are any rounding errors...

var Outstanding = MAX( LoanAmount - AmountPaid, 0 )

return

// If Outstanding is 0, BLANK is returned.

If( Outstanding, Outstanding )

)

[daXtreme]

@SaibAnalytics 

I can't help you with the error because I don't know which place it's attached to. It's a rather standard error that you should be able to figure out yourself when debugging the measure.

[daXtreme · Accepted solution]

// Assumptions:
// 1. Dates (your calendar) is not connected to the Loan
//    fact table.
// 2. After the last installment, the amount outstanding
//    is always 0.
// 3. Loan installment days can only be multiples of
//    30, meaning 1, 2, 3, 4... months.
// If the logic is different, you'll have to adjust the
// code.

[Outstanding] =
var LastDateSelected = MAX( Dates[Date] )
return
SUMX(
    Filter(
        Loans,
        // Other loans will for sure have their outstanding
        // balance of 0, so only these satisfying the below 
        // condition are of interest.
        and(
            Loans[First Inst. Date] <= LastDateSelected,
            LastDateSelected <= Loans[Last Inst. Date]
        )
    ),
    // For each loan we iterate, we need to figure out
    // how many installments have already been paid.
    // Bear in mind that if LastDateSelected is a day
    // on which an installment should be paid, the installment
    // is treated as having been paid.
    var FirstInstallmentDay = Loans[First Inst. Date]
    var FirstInstallmentDayInCalendar =
        CALCULATETABLE(
            DISTINCT( Dates[Date] ),
            Dates[Date] = FirstInstallmentDay
        )
    var NumOfInstallments = Loans[Number of installments]
    // Granularity tells you the number of months between
    // consecutive payments.
    var Granularity = int( Loans[Loan Installment Days] / 30 )
    var AllPaymentDays =
        GENERATE(
            // -1 because we start from 0
            GENERATESERIES(0, NumOfinstallments - 1, 1),
            var InstallmentNumber = [Value]
            var PaymentDay =
                DATEADD(
                    FirstInstallmentDayInCalendar,
                    Granularity * InstallmentNumber,
                    MONTH
                )
            return
                ROW( "@PaymentDay", PaymentDay )
        )
    var PaymentDaysBeforeOrOnLastDateSelected =
        FILTER(
            AllPaymentDays,
            [@PaymentDay] <= LastDateSelected
        )
    var NumOfInstallmentsPaid =
        COUNTROWS( PaymentDaysBeforeOrOnLastDateSelected )
    var InstallmentAmount = Loans[Installment Amount]
    var AmountPaid = InstallmentAmount * NumOfInstallmentsPaid
    var LoanAmount = Loans[Loan Amount]
    // Using MAX just in case there are any rounding errors...
    var Outstanding = MAX( LoanAmount - AmountPaid, 0 )
    return
        // If Outstanding is 0, BLANK is returned.
        If( Outstanding, Outstanding )
)

[SaibAnalytics]

Thanks a lot for this! let me try the solution.

[daXtreme]

Hi there.

I've been looking at the file. Loan "aa", for instance, has 12 installments @ 135 each. The borrowed amount is 1500. Since 12 * 135 = 1620 =/= 1500, this violates one of the assumptions which states that after the last installment the amount outstanding should be 0. That's one thing I've noticed.

Secondly, the formula seems to work OK but you cannot, as I said, have an active relationship between the time axis and the Loans table. This is because the time axis must show, for any selected loan(!), its balances as the time progresses. This therefore must be an independent axis.

If you want to be able to slice loans by one of the 3 dates that you store in the Loans table, then you have 2 choices:

1. Have 2 date tables (with different and meaningful names). One will be the independent one on which you can show how the balances progress and the other one will be used for slicing the loans by ONE of the date fields (most likely Allocation Date).
2. Have one date table (the independent time axis) and just expose the dates from the Loans table as attributes of the loans. You'll then be able to slice by them.

Point 2) is the right setup you should have. Then the measure will work as expected.

Best

[SaibAnalytics]

Thanks a lot! I appreciate the help.
I have changed my data and used used option 2 and it worked!

[daXtreme]

Glad it's worked.