Sum of minimal values for categories from 2 columns

[MarinaEscali]

Hi, 

I am a rookie in Power BI and I've been sitting for a while whith this problem:

So I have a table that contains loan data payment schedules. (Loan A and B are just some of them). Each loan is payd during several years quarterly, semiannually or annualy and their payment schedule dates differ: 

Loan	TransactionDate	year	Balance
A	07.01.2021	2021	50 000
A	07.04.2021	2021	45 000
A	07.07.2021	2021	40 000
A	07.10.2021	2021	35 000
A	07.01.2022	2022	30 000
A	07.04.2022	2022	25 000
A	07.07.2022	2022	20 000
A	07.10.2022	2022	15 000
A	07.01.2023	2023	10 000
A	07.04.2023	2023	5 000
B	15.02.2021	2021	120 000
B	15.05.2021	2021	110 000
B	15.08.2021	2021	100 000
B	15.11.2021	2021	90 000
B	15.02.2022	2022	80 000
B	15.05.2022	2022	70 000
B	15.08.2022	2022	60 000
B	15.11.2022	2022	50 000
B	15.02.2023	2023	40 000
B	15.05.2023	2023	30 000
B	15.08.2023	2023	20 000
B	15.11.2023	2023	10 000

Now I want to show the balance at the beginning of the year for each of these loans. I also would like to see the sum of the outstanding balance of all the loans:

	2021	2022	2023
A	50 000	30 000	10 000
B	120 000	80 000	40 000
Total	170 000	110 000	50 000

I have tried to use min and rank and other combinations. The problem is that they don't sum correctly.

For example:

BalanceYear =

VAR First_Date = MIN(Table[TransactionDate])

VAR Balance_Table = CALCULATETABLE(SUMMARIZE(Table,Table[Year],

"Bal", SUM(Table[Balance])), Table[TransactionDate] = First_Date)

RETURN

CALCULATE(SUMX(Balance_Table, [Bal]))

Visualizing the dtaa throug a matrix show the expected Balance at the beginning of each year. BUT! The sum field shows the Balance for the minimal TransactionDate (even if the balance is not necessarily the smallest amount)

[Anonymous · Accepted solution]

Hi @MarinaEscali 

Please Try this measure:

Measure = 
VAR _T = SUMMARIZE(FILTER('Table','Table'[TransactionDate] = CALCULATE(MIN('Table'[TransactionDate]),FILTER('Table','Table'[Loan] = EARLIER('Table'[Loan])&&'Table'[year]=EARLIER('Table'[year])))),'Table'[Loan],'Table'[TransactionDate],'Table'[year],'Table'[Balance])
Return
IF(HASONEVALUE('Table'[Loan]),SUMX(FILTER(_T,[Loan] = MAX('Table'[Loan])&&'Table'[year] = MAX('Table'[year])),[Balance]),SUMX(FILTER(_T,'Table'[year] = MAX('Table'[year])),[Balance]))

Result is as below.

Best Regards,

Rico Zhou

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