Allocate payments to invoices in chronological order

[cpatenta]

Hello All,

I have the following problem (tables are simplified):

Table Invoices:

InvoiceID	PhaseCode	Instalment	DueDate	Amount
101	CD	A0	02/10/2021	1000
101	CD	P0	02/15/2021	4000
101	CD	P15	03/31/2021	5000

Table Payments

InvoiceID	PhaseCode	PaymentDate	Amount
101	CD	02/25/2021	500
101	CD	03/03/2021	1500
101	CD	03/10/2021	1000
101	CD	03/15/2021	6500

So Payments table doesn't have Instalment code specified.

One payment can partially settle an invoice or it can settle one or several invoices.

How can I build the following output table in Power Query?

Table Output

InvoiceID	PhaseCode	Instalment	PaymentDate	Amount
101	CD	A0	02/25/2021	500
101	CD	A0	03/03/2021	500
101	CD	P0	03/03/2021	1000
101	CD	P0	03/10/2021	1000
101	CD	P0	03/15/2021	2000
101	CD	P15	03/15/2021	4500

I appreciate any help you can offer.

Thank you!

[Federico_Fa]

Hello everybody,

do you know if there is a way to consider only the payments which have an invoice number ID similar to the invoice?

In this example i don't want to consider the payment with invoice "1033" which is not in the invoice list.

Thank you so much

[Anonymous]

I have found a different way of solving the problem which does not use the list.generate function.

I am curious to know the different execution time of the two algorithms on your real database.

[cpatenta]

Thank you for the new solution!

I had some busy days at work. I hope I will try it this weekend.

The invoices and payments tables have about 50 records now, for testing only. We will start adding real data the following weeks. 

I'll compare the solutions when we have more data and post the results here.

[Anonymous · Accepted solution]

Check if this solves your problem.

[cpatenta]

@Anonymous 

Thank you for your time and for the solution.

Is there a way to make List.Accumulate function to also include Instalment Codes in the result?

It is not reliable to join tables by Amount column.

[Anonymous]

Ok.

I think I understand the meaning of observation.

I think the best solution is to add the use of the Instalment variable in the IadoP function. If you can't do it, when I have time, I'll do it. I don't know if you need to apply this code to very large tables. I'm afraid it's not very performing.

But the rules to be implemented are really very very complex, for a language like M.

[cpatenta]

I tried to change your code to include Instalment Codes, but couldn't figure it out so far. I am still trying to understand list functions 🙂

I am quite new to PowerBI and PowerQuery, less than one year, but determined to learn 🙂

The project is ideed complex, with lots of tables from different sources, fortunately tables are not very large and we don't expect them to grow much (most of the tables have less than 300 rows).

I almost completed it, this payments splitting problem is the only one I couldn't figure out by myself.

Thanks again for your time and involvement.

[Anonymous]

I followed your indication and added Instalment in the list.accumulate function.

[Anonymous]

To make the solution more general and robust, you should better explain the logic with which payments are divided between the different invoices.

Why, for example, is the payment of March 15th split between instalment P0 (with 2000) and P15 (with 4500)?

And why is the payment of March 3 split between instalment A0 wiyth 500 and P0 with 1000?

[cpatenta]

@Anonymous 

Thank you for your answer.

I think I simplified the tabels too much.

The Invoices table also have a DueDate column, I edited my post and added the column.

Since Payments table doesn't have an Instalment reference, I have to allocate payments to Instalments in chronological order:

A0 (1000), has first due date and it should have the following payments allocated:

• February 25th (500)
• March 3rd (500 out of total payment of 1500)

P0 (4000) has the second due date and the following payments:

• March 3rd (rest of 1000 out of 1500),
• March 10th (1000)
• March 15th (2000 out of total payment of 6500)

P15 (5000) has the last due date and is payed partially in March 15th (4500 unallocated payment) and rest of 500 remains unpayed.

In the end, the Output table should show when and how each instalment was payed

Thanks again for your involvement!