Aggregate table

[omega123]

Hi all.
I would like to get one aggregate table from the billing and incoming payments table, where the incoming payments should be assigned to the contracts with the earliest due dates (payment deadlines) and according to the priority of the incoming payment.
I have tried to solve it with Power Query, received one good solution (community.powerbi.com/t5/Power-Query/Aggregate-table/m-p/2935558#M92692), but it was not possible to take into account the priority of the incoming payment.
I suppose it is possible to solve it with VBA.
As I am new to VBA and would be very grateful for your help.

Attached is the excel file with initial table, results and with a description.

Here is also a description:

We first select the payments with priority 1 on 27.02.2022, 28.02.2022, 02.03.2022.
A payment of 50 under contract 1 on 27.02.2022 will cover partly the amount of 100 from 02.02.2022 with a due date of 15.02.2022.
A payment of 150 under contract 3 on 28.02.2022 will*cover partly the amount of 200 from 03.02.2022 with a due date of 15.02.2022.
And a payment of 50 under contract 5 on*02.03.2022 will cover*partly the amount of 100 from 10.02.2022 with a due date of 24.02.2022.
After that we select the payment with priority 2. This is a payment of 100 under contract 1 on 26.02.2022 which will cover the rest of the amount of 100 from 02.02.2022 with a due date of 15.02.2022 and partly the amount of 500 from 13.02.2022 with a due date 07.03.2022.
Than the payment with priority 3 comes into play. This is a payment of 50 under contract 5 on 03.03.2022 which will cover the rest of the amount of 100 from 10.02.2022 with a due date of 24.02.2022
Under priority 4 we have a payment of 200 under contract 2 which will cover partly the amount of 300 from 08.02.2022 with a due date of 22.02.2022.
And finally we have the payment of 350 with priority 5 under contract 4 which will cover partly the amount of 400 from 05.02.2022 with a due date of 19.02.2022.

If you have any ideas also how to do it in Power Query - I will be also very grateful for that.

[omega123]

Hello.
I have read all the 333 posts on this forum regarding FIFO method.
It is the exact approach that is needed - in my case incoming payments should cover the contracts with the earliest due dates (payment deadlines) and according to the priority of the incoming payment.
Unfortunately I could not find the right answer...

Thank you in advance for your help.

[jindon]

See if this works,

Please Login or Register  to view this content.

[omega123]

Hi!
Thank you very much, it works good.
What piece of code should be added to this script in order to format our final table as "Table". This is what I mean: support.microsoft.com/en-us/office/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664
How to do it automatically in this script and for example name the resulting table as "Final_Calculation"?

[jindon]

Add lines in bold

Please Login or Register  to view this content.

[omega123]

Hi!
Thank you once more for the helpful solution!
I would also need help with a more simple situation.

I would like to get one aggregate table from the billing and incoming payments table, where the incoming payments should be assigned strictly to respective contracts and dates (to which the incoming payment refers) and according to the priority of the incoming payment.

Attached is the excel file with initial table, results and with a description.
We first select the payments with priority 1 on 27.02.2022, 28.02.2022, 02.03.2022.
A payment of 50 under contract 1 on 27.02.2022 which refers to the date of 02.02.2022 will cover partly the amount of 100 from 02.02.2022 with a due date of 15.02.2022.
A payment of 100 under contract 3 on 28.02.2022 which refers to the date of 16.02.2022 will cover fully the amount of 100 from 16.02.2022 with a due date of 17.03.2022.
And a payment of 50 under contract 5 on 02.03.2022 which refers to the date of 10.02.2022 will cover partly the amount of 100 from 10.02.2022 with a due date of 24.02.2022.
 
After that we select the payment with priority 2. This is a payment of 100 under contract 1 on 26.02.2022 which refers to the date of 02.02.2022 and will cover the rest of the amount of 100 from 02.02.2022 with a due date of 15.02.2022.
 
Than the payment with priority 3 comes into play. This is a payment of 50 under contract 5 on 03.03.2022 which refers to the date of 10.02.2022 and will cover the rest of the amount of 100 from 10.02.2022 with a due date of 24.02.2022 
 
Under priority 4 we have a payment of 200 under contract 2 which refers to the date of 19.02.2022 and will cover fully the amount of 200 from 19.02.2022 with a due date of 25.02.2022.
 
And finally we have the payment of 350 with priority 5 under contract 4 which refers to the date of 05.02.2022 and will cover partly the amount of 400 from 05.02.2022 with a due date of 19.02.2022.

How should be the code modified?

Thank you for your help!

[omega123]

Hi all!
I still need an answer to my question (12/02/2022) and would be very grateful for your help!

[omega123]

Hello everyone.

I would be very grateful for your help regarding this task - allocation of payments according to the number of respective contracts and dates (to which the incoming payment refers) and according to the priority of the incoming payment.

Thank you in advance.

[omega123]

I attach also to this thread 2 additional files with descriptions of one more feature, that I'm interested in - how to take into account the limit, i.e. if the cumulative sum of "Amounts payable" from the beginning of the table exceeds the limit for the current day, then we should not consider the amounts payable, which exceed the limit.
So I would need 4 scripts here:

1) FIFO method without limit - is already suggested by jindon
2) FIFO method incl. limit
3) standard method (strict assignment of payments by contract number and date to which the payment refers) without limit
4) standard method (strict assignment of payments by contract number and date to which the payment refers) incl. limit

Thank you for your help!