cancel
Showing results for
Did you mean:
New Member

Outstanding Balance calculation

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
1 ACCEPTED SOLUTION
Community Support

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.

Community Support

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.

Announcements