Forum Discussion

Kolumam2810's avatar
Kolumam2810
New Member
5 years ago
Solved

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?

TrancheRepayment Schedule Start DateRepayment Schedule End Date%PercentageProject NamePrincipal Amount (USD)
A31/12/202029/6/20211.360.0136Project A100
A30/6/202130/12/20211.970.0197Project A100
A31/12/202129/6/20224.050.0405Project A100
A30/6/202230/12/20224.020.0402Project A100
A31/12/202229/6/20234.20.042Project A100
A30/6/202330/12/20234.170.0417Project A100
A31/12/202329/6/20244.360.0436Project A100
A30/6/202430/12/20244.360.0436Project A100
A31/12/20242/6/20254.530.0453Project A100
A3/6/20253/6/202566.980.6698Project A100
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Icey's avatar
    Icey
    5 years ago

    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.

1 Reply

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    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.