topic Capital Balance reduction using Cumulative Sum of interest - movement(payment) in DAX Commands and Tips

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

Mat-42 — Fri, 16 Dec 2022 22:50:58 GMT

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.

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

Above it the Capital Balance I need.

Thank you

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

lbendlin — Mon, 19 Dec 2022 01:20:41 GMT

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-p/963216
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

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

Anonymous — Mon, 19 Dec 2022 07:04:56 GMT

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.

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

lbendlin — Mon, 19 Dec 2022 14:34:53 GMT

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

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

Mat-42 — Mon, 19 Dec 2022 22:37:15 GMT

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 ).

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.

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

lbendlin — Tue, 20 Dec 2022 00:26:33 GMT

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-p/963216
Please show the expected outcome based on the sample data you provided.

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

Mat-42 — Tue, 20 Dec 2022 00:43:06 GMT

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

Hi Ibendlin,

Please find the sample data.
Thank you

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

Mat-42 — Tue, 20 Dec 2022 00:50:10 GMT

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

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

lbendlin — Thu, 22 Dec 2022 22:11:27 GMT

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".

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

Mat-42 — Thu, 29 Dec 2022 11:17:07 GMT

Thank you ,
This was a great help.