Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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".
The usual approach is via List.Accumulate and custom iterators.
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
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:
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.
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 ).
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |