Outstanding Balance calculation

[Kolumam2810]

I have the below table. I need to calculate the outstanding amount as per the below formula.

Outstanding Amount = (Principal Amount - Principal Amount * Percentage)

So for the first date range (31/12/2020 - 29/6/2021), the outstanding amount will be (100 - (100*0.0136)) = 98.64

for the next date range (30/6/2021 - 30/12/2021), the outstanding amount will be (98.64 - (100*0.0197) = 96.67 

As you can see, I need to take the previous value to calculate the outstanding for each date range. How do I accomplish this for each project?

Tranche	Repayment Schedule Start Date	Repayment Schedule End Date	%	Percentage	Project Name	Principal Amount (USD)
A	31/12/2020	29/6/2021	1.36	0.0136	Project A	100
A	30/6/2021	30/12/2021	1.97	0.0197	Project A	100
A	31/12/2021	29/6/2022	4.05	0.0405	Project A	100
A	30/6/2022	30/12/2022	4.02	0.0402	Project A	100
A	31/12/2022	29/6/2023	4.2	0.042	Project A	100
A	30/6/2023	30/12/2023	4.17	0.0417	Project A	100
A	31/12/2023	29/6/2024	4.36	0.0436	Project A	100
A	30/6/2024	30/12/2024	4.36	0.0436	Project A	100
A	31/12/2024	2/6/2025	4.53	0.0453	Project A	100
A	3/6/2025	3/6/2025	66.98	0.6698	Project A	100

[Icey · Accepted solution]

Hi @Kolumam2810 ,

Try this:

Column:

Column = 
'Table'[Principal Amount (USD)]
    - SUMX (
        FILTER (
            'Table',
            'Table'[Tranche] = EARLIER ( 'Table'[Tranche] )
                && 'Table'[Repayment Schedule Start Date]
                    <= EARLIER ( 'Table'[Repayment Schedule Start Date] )
        ),
        'Table'[Principal Amount (USD)] * 'Table'[Percentage]
    )

Measures:

Measure = 
SUM ( 'Table'[Principal Amount (USD)] )
    - SUMX (
        FILTER (
            ALLSELECTED('Table'),
            'Table'[Tranche] = MAX ( 'Table'[Tranche] )
                && 'Table'[Repayment Schedule Start Date]
                    <= MAX( 'Table'[Repayment Schedule Start Date] )
        ),
        'Table'[Principal Amount (USD)] * 'Table'[Percentage]
    )

Measure - ModifiedTotalValue = SUMX('Table',[Measure])

Best Regards,

Icey

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