calculate outstanding balance of loans

[allard]

Hello All, 

I am looking to calculate my outstanding amount per loan, but I can't get it right. I got 11 outstanding loans.

I got five Excel tables

• Calender Table
• Investment Table 
• Wallet table
• Measure table
• Lenderstable 

In the investment Excel file there are the following columns: 

- Investment number

- Investment 

- Paymentnumber 

- Date 

- Time 

- Outstanding amount

- Gross installment payment

- Capital Reimbursement 

- Interest 

- Tax

- Net instalment 

This Excel is filled like an amortization table. So multiple transactions. 

I got a seperate Excel table with the transactions of my wallet. 

In here are the investments I made, so the total of transactions is my actual balance amount. 

I used these measures:

To get the transactions which are made for project investements from my wallet: 

Transactions investments =

CALCULATE(

    [Transactions total],

    'Goparity Transacties',

    'Goparity Transacties'[Transaction type] = "project investment")

To get the total capital reimbursement: 

Total Capital Reimbursement = sum('Goparity investeringen'[Capital Reimbursement])

The problem I am facing if I use a sum function to deduct the two measures from each other I get zero. At the end of the loan ofcourse it is zero, but I want to see the remaining amount per period. 

Any help is welcome 🙂 

[allard]

I kind of made what I want, but I got the idea it can be done with one formula instead of 20. 

First I calculated all the Capital Reimbursement per years 2022-2029. 

Second I calculated the outstanding capital in 2022- Capital reimbursement in 2022.

Totaal amount outstanding 2022 = CALCULATE([invested 2022]-[Capital Reimbursement2022])

Copy this for 2022-2029 and i got my diagram. The only problem is I can't use it in a line graph, because I filtered the years with my measures.

[Ashish_Mathur]

Hi,

i still do not know what you want but this is my interpretation of your requirement.  I have taken the last outstanding amount of each investor for each year from the Goparity investeringen table.

You may download my PBI file from here.

[allard]

Dear Asish, 

This is kind of what I want, but I am wondering if the amounts that are being calculated are right. For example the outstanding balance in 2022 = 903,95. If I sum up the data in the graph this does not add up to 903,95

[Ashish_Mathur]

Hi,

As i mentioned earlier, i cannot understand what you want.  If you can show the result in a simple table with an explanation, I can probably help.

[allard]

Hello Ashish, 

Apologies for the late reply. 

I want something like this within power bi, I made the adjustments in excel.

I don't know if it is even possible, but could this also be one line? That is the sum of all the outstanding loans? 

[Anonymous]

Hi  @allard ,

If you want to see the fields for each period, you can consider grouping each period by adding [per period] =max([per period]) to it when using measure.

For example:

Measure =
var _groupvalue1=
SUMX(FILTER(ALL('Table'),'Table'[Group]=MAX('Table'[Group])),[Value1])
var _groupvalue2=
SUMX(FILTER(ALL('Table'),'Table'[Group]=MAX('Table'[Group])),[Value2])
return
_groupvalue1 - _groupvalue2

MAX function (DAX) - DAX | Microsoft Learn

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[allard]

Hi Liu Yang 🙂 ,

I couldn't get your formula, but that is mabye because of me. I posted my PBIX file for you to have a look and hope it clears up a couple of  things. 

Thank you in advance 🙂

[allard]

I anonymised my data.

Here is the wetransfer link for the PBIX and excel data 🙂

https://we.tl/t-jVAdgS4yw1

[Ashish_Mathur]

Hi,

Does the table on the right get you your desired result?  Download the PBI file from here.

[allard]

Hi Asish, 

I don't think your solution is exactly what I want, but anyway thank you for your answer.

What I actually want is a chart with my outstanding money per year and per loan. 

So I want my total invested amount (Measure is called: Totaal geinvesteerd) - My total Capital Reimbursment.

I don't think i can use the Outstanding amount column in table 'goparity investeringen', because there are multiple transactions in the table and the my outstanding amount will be much larger than it exactually is.