I have the following two queries, one with a number of invoices sorted by date and the other one with a number of receipts
| Customer ID | Trans. Type | Trans. Date | N# | Value | Fiscal Year |
|---|---|---|---|---|---|
| 1 | Invoice | 03/07/2021 | 1 | 20.00 | 2021 |
| 1 | Invoice | 03/07/2020 | 2 | 10.00 | 2020 |
| 1 | Invoice | 14/01/2020 | 3 | 50.00 | 2020 |
| 1 | Invoice | 21/10/2019 | 4 | 200.00 | 2019 |
| 2 | Invoice | 01/10/2018 | 5 | 99.00 | 2018 |
| Customer ID | Trans. Type | Trans. Date | N# | Value |
|---|---|---|---|---|
| 1 | Receipt | 04/12/2020 | 50 | 260.00 |
| 1 | Receipt | 03/12/2020 | 49 | 110.00 |
My goal is to add one column to the receipt query with the fiscal year the invoice refers to, starting from the more recent ones until the receipt value is reached. The year must be taken from the "Fiscal Year" column.
The following would be the result I want to achieve.
| Customer ID | Trans. Type | Trans. Date | N# | Value | Fiscal Year |
|---|---|---|---|---|---|
| 1 | Receipt | 04/12/2020 | 50 | 11.00 | 2021 |
| 1 | Receipt | 04/12/2020 | 50 | 60.00 | 2020 |
| 1 | Receipt | 04/12/2020 | 50 | 189.00 | 2019 |
| 1 | Receipt | 03/12/2020 | 49 | 11.00 | 2019 |
| 1 | Receipt | 03/12/2020 | 49 | 99.00 | 2018 |
I would like to achieve this with M but the exercise is not trivial for me because the value of the receipt can refer to multiple invoices and I don't know how to split it.
EDIT
The second query can have multiple records because there can be several receipts over a period of time for the same customer. All or part of the first query records can be included in the second query (there cannot be any receipt without having an invoice in first place).
EDIT 2
The process combine invoices and receipts of the same customers
The process also assumes that the invoices paid first are the older ones therefore the older invoices are used up first and the invoice balance left is used up by the next receipt.