cancel
Showing results for
Did you mean:
imadtn 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])) 1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor

## Re: Calculating loan paid and loan draw down

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

Proud to be a Datanaut!

2 REPLIES 2
OwenAuger Super Contributor

## Re: Calculating loan paid and loan draw down

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

Proud to be a Datanaut!

imadtn 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!

Announcements #### New Topics Started Badges Coming #### 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! #### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020 Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,345)