Calculating loan paid and loan draw down

[imadtn]

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]))

 

[OwenAuger · Accepted solution]

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

[imadtn]

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!