Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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 )
)
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.
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.
// 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 )
)
Thanks a lot for this! let me try the solution.
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:
Point 2) is the right setup you should have. Then the measure will work as expected.
Best
Thanks a lot! I appreciate the help.
I have changed my data and used used option 2 and it worked!
Glad it's worked.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |