cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imadtn Frequent Visitor
Frequent Visitor

Calculating loan paid and loan draw down

Dear all, new to DAX and appreciate some help..

I am trying to calculate loan repayment YTD and loan draw down YTD as measures. But all I have in my data is loan balances by loan from general ledger and by date.

 

Making a simple measure as "Term loans Today"- "Term loans end of last year" gives the net difference, so it doesnt work as i need amount paid and amount drawn for each loan.

 

So,  I am ok with taking the difference for each loan alone, if it is an increase its a drawdown and if it is a decrease it is a repayment. Then i need to sum up draw downs and repayments of each loan to get the total. The problem is how to do the logic for summing the differences?


See the table below and following is the formulas for the measures. For each loan [Paid amount] and [Drawdown amount] is showing correctly but the totals are not correct as the logic needs to be different than what it is inside the table. Any ideas?

Paid amount = if([Term loans today]>=[Term loans end of last year],BLANK(),ABS([Term loans end of last year]-[Term loans today]))
Drawdown amount = if([Term loans today]<[Term loans end of last year],BLANK(),ABS([Term loans end of last year]-[Term loans today]))

 

 loans capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Calculating loan paid and loan draw down

Hi @imadtn

 

You should be able to fix this using SUMX.

SUMX can iterate over the loans, computing Paid amount & Drawdown amount for each loan, and then summing.

 

It looks like Loan code or something similar is the Loan dimension.

Give these measures a go, replacing YourTable[Loan code] with the appropriate column reference:

Paid amount =
SUMX (
    VALUES ( YourTable[Loan code] ),
    VAR LoansToday = [Term loans today]
    VAR LoansLastYear = [Term loans end of last year]
    RETURN
        IF ( LoansToday < LoansLastYear, ABS ( LoansLastYear - LoansToday ) )
)
Drawdown amount =
SUMX (
    VALUES ( YourTable[Loan code] ),
    VAR LoansToday = [Term loans today]
    VAR LoansLastYear = [Term loans end of last year]
    RETURN
        IF ( LoansToday >= LoansLastYear, ABS ( LoansLastYear - LoansToday ) )
)

Note:

  • I used variables to avoid repeated evaluation of measures.
  • I inverted the comparisons within each IF, so that result_if_true is nonblank (it just makes the code a little shorter as result_if_false defaults to BLANK() )

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
OwenAuger Super Contributor
Super Contributor

Re: Calculating loan paid and loan draw down

Hi @imadtn

 

You should be able to fix this using SUMX.

SUMX can iterate over the loans, computing Paid amount & Drawdown amount for each loan, and then summing.

 

It looks like Loan code or something similar is the Loan dimension.

Give these measures a go, replacing YourTable[Loan code] with the appropriate column reference:

Paid amount =
SUMX (
    VALUES ( YourTable[Loan code] ),
    VAR LoansToday = [Term loans today]
    VAR LoansLastYear = [Term loans end of last year]
    RETURN
        IF ( LoansToday < LoansLastYear, ABS ( LoansLastYear - LoansToday ) )
)
Drawdown amount =
SUMX (
    VALUES ( YourTable[Loan code] ),
    VAR LoansToday = [Term loans today]
    VAR LoansLastYear = [Term loans end of last year]
    RETURN
        IF ( LoansToday >= LoansLastYear, ABS ( LoansLastYear - LoansToday ) )
)

Note:

  • I used variables to avoid repeated evaluation of measures.
  • I inverted the comparisons within each IF, so that result_if_true is nonblank (it just makes the code a little shorter as result_if_false defaults to BLANK() )

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

imadtn Frequent Visitor
Frequent Visitor

Re: Calculating loan paid and loan draw down

Hi Owen, 
Thanks for the reply. I had solved the issue by deriving a new table from my data set and making the paid and drawdown amount as a calculated column. 

Your solution is much better though, I tested and it works, and thanks for the tips.
Thanks!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)