Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Mat-42
Frequent Visitor

Capital Balance reduction using Cumulative Sum of interest - movement(payment)

Hi Community,
I am trying to calculate capital balance reduction based on Interest Column and Movement Column.
If a payment is missed the interest needs to role to the next month.
When a payment is made it needs to take it off the Rolling interest. Any remaining positive movement will go against the Capital Balance.
I have been battling for some time with this, would really appreciate some help.

Mat42_1-1671214203782.png

Rolling Interest Needed Column is what I am trying to get in order to calculate my Capital Balance

Mat42_0-1671230869517.png

Above it the Capital Balance I need.

Mat42_2-1671214825056.png

Thank you

 
 
1 ACCEPTED SOLUTION

Here is a sample implementation using List.Accumulate

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJLDoMwDATQq1SsIzP+Jln2HIjT9SY9WRGgNqmAdhHLs3jKYjxNw31IA3QEjwKRJTwfnIr4umGdAiQAhC2uy5w+VFqaC1O1Jdz2dwC0A1rIcQ2sBVGNIrYAnCPvkINMfqPoEJxy/o1yizxAeQuRzJSg57J0kp0k/pS1lRaVtBxLWyrJZNZqRqdlAeVd+PGH3B2I5iCNy9K4OwuVSnvQJJnJ5UvOLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ContractNo" = _t, #"Date" = _t, #"Interest" = _t, #"Movement" = _t, #"Balance" = _t, Adjustment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Interest", Currency.Type},{"Movement", Currency.Type},{"Balance", Currency.Type},{"Adjustment", Currency.Type}}, "en-GB"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Rolling Interest", each List.Accumulate({0..[Index]},0,(state,current)=> state + #"Added Index"[Interest]{current} - (#"Added Index"[Movement]{current}??0) + #"Added Index"[Adjustment]{current})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Rolling Interest", Currency.Type}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

View solution in original post

9 REPLIES 9
lbendlin
Super User
Super User

The usual approach is via List.Accumulate and custom iterators.

ContractNo(source)Date(Source)Interest(Source)Movement(Source)Balance(Source)AdjustmentRolling Intrest Capital Balance (Goal to Get)
A03/01/2022£1,825£0£200,000.00£0.00£1,825£200,000.00
A03/02/2022£1,781.94  £0.00£3,606.94£200,000.00
A03/03/2022£1,738.50  £0.00£5,345.44£200,000.00
A03/04/2022£1,694.66£100 £0.00£6,940.10£200,000.00
A03/05/2022£1,650.42£100 £0.00£8,490.52£200,000.00
A03/06/2022£1,605.77£100 £0.00£9,996.29£200,000.00
A03/07/2022£1,560.72£6,443.03 £0.00£5,113.98£200,000.00
A03/08/2022£1,515.26£6,443.03 £0.00£186.21£200,000.00
A03/09/2022£1,469.38£6,443.03 £4,787.44£0.00£195,212.56
A03/10/2022£1,423.08£0 £0.00£1,423.08£195,212.56
A03/11/2022£1,376.36  £0.00£2,799.44£195,212.56
A03/12/2022£1,329.22£3,271.52 £0.00£857.14£195,212.56

Hi Ibendlin,

Please find the sample data.
Thank you 

 

Anonymous
Not applicable

Hi @Mat-42 ,

 

According to your statement, you need to rolling based on the value on pervious date. As far as I know, Dax is not a good choice to calulate for looping. Here I suggest you to try Power Query to achieve your goal.

For referernce:

Power Query Loop Calculation

List.Generate() and Looping in PowerQuery

 

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.

lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Hi Ibendlin
I can’t seem to load the data in the table ribbon given.
Here is a screen shot of what I am trying to achieve

The first 5 columns are what I get from the source system.
Contract Number, Date, Interest, Movement Balance.
I need to get the last three columns Rolling Interest (which is the interest accumulated, that the movement payment was not able to clear).
Adjustment which is the amount used to adjust the Capital Balance (it is the positive remaining movement after Rolling interest has been deducted ).

Mat42_0-1671489410373.png

 

ACCRUING INTEREST LOGIC
If Payments received > Interest due in month THEN payment - interest and remainder comes off capital
If Payments received < Interest THEN Interest - payment, remaining interest accrues
Then if payments comes in < accrued interest + current month interest, then Accrued int + current month int - payment, remaining interest continues to accrue
Then if payments comes in > accrued interest + current month interest, then payment - Accrued int + current month int, remaining balance comes off capital

Would really appreciate any help.

I am unable to help when the sample data is provided as a screenshot. 
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

Hi Ibendlin,

Thank you for your reply.
I have been able to post some sample data in your earlier reply, were you mention List.Accumulate.
I was unable to post in the other conversation thread.

 

Thank you for your help

Here is a sample implementation using List.Accumulate

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJLDoMwDATQq1SsIzP+Jln2HIjT9SY9WRGgNqmAdhHLs3jKYjxNw31IA3QEjwKRJTwfnIr4umGdAiQAhC2uy5w+VFqaC1O1Jdz2dwC0A1rIcQ2sBVGNIrYAnCPvkINMfqPoEJxy/o1yizxAeQuRzJSg57J0kp0k/pS1lRaVtBxLWyrJZNZqRqdlAeVd+PGH3B2I5iCNy9K4OwuVSnvQJJnJ5UvOLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ContractNo" = _t, #"Date" = _t, #"Interest" = _t, #"Movement" = _t, #"Balance" = _t, Adjustment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Interest", Currency.Type},{"Movement", Currency.Type},{"Balance", Currency.Type},{"Adjustment", Currency.Type}}, "en-GB"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Rolling Interest", each List.Accumulate({0..[Index]},0,(state,current)=> state + #"Added Index"[Interest]{current} - (#"Added Index"[Movement]{current}??0) + #"Added Index"[Adjustment]{current})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Rolling Interest", Currency.Type}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Thank you , 
This was a great help.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.