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 respective contracts and dates (to which the incoming payment refers). I have already asked about a more sophisticated FIFO case (https://community.powerbi.com/t5/Power-Query/Aggregate-table/m-p/2909309#M91722) and received a helpful solution. 

Now I have a more simple situation - please see 2 tables below.

A payment of 200 under contract 2 which refers to the date of 19.02.2022 will cover fully the amount of 200 from respective date 19.02.2022 with a due date of 25.02.2022.
A payment of 50 under contract 1 on 26.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 50 under contract 1 on 27.02.2022 which refers to the date of 02.02.2022 will cover the rest of the amount of 100 from 02.02.2022 with a due date of 15.02.2022.
And so on...

I have tried to assign the contract numbers and reference dates, but could not do it. Although it's really a very easy task.

I would be very grateful for your help.

INITIAL TABLE

Date - daily	Contract nmb	Date to which the incoming payment refers	Amount payable	Payment deadline	Incoming payment
01.02.2022
02.02.2022	1		100	15.02.2022
03.02.2022	3		200	15.02.2022
04.02.2022
05.02.2022	4		400	19.02.2022
06.02.2022
07.02.2022
08.02.2022	2		300	22.02.2022
09.02.2022
10.02.2022	5		100	24.02.2022
11.02.2022
12.02.2022
13.02.2022	1		500	07.03.2022
14.02.2022	1		400	10.03.2022
15.02.2022
16.02.2022	3		100	17.03.2022
17.02.2022	5		800	20.03.2022
18.02.2022
19.02.2022	2		200	25.03.2022
20.02.2022
21.02.2022
22.02.2022
23.02.2022
24.02.2022
25.02.2022	2	19.02.2022			200
26.02.2022	1	02.02.2022			50
27.02.2022	1	02.02.2022			50
28.02.2022	3	16.02.2022			100
01.03.2022	4	05.02.2022			350
02.03.2022	5	17.02.2022			50
03.03.2022	5	10.02.2022			50
04.03.2022
05.03.2022

AGGREGATE TABLE

Date	Contract nmb	Amount payable	Payment deadline	Received payment	Remaining amount payable
02.02.2022	1	100	15.02.2022	100	0
03.02.2022	3	200	15.02.2022		200
05.02.2022	4	400	19.02.2022	350	50
08.02.2022	2	300	22.02.2022		300
10.02.2022	5	100	24.02.2022	100	0
13.02.2022	1	500	07.03.2022		500
14.02.2022	1	400	10.03.2022		400
16.02.2022	3	100	17.03.2022	100	0
17.02.2022	5	800	20.03.2022		800
19.02.2022	2	200	25.03.2022	200	0

[omega123]

Hello

thank you @v-jingzhang! Now I understand it. That's exactly what I need - payments should be assigned strictly to the specific rows.

By the way, I have asked another related question here https://community.powerbi.com/t5/Power-Query/Aggregate-table/m-p/2935558#M92692  - how to take into account the priority of the incoming payment. At first, payments with top priority should be taken into account. Do you know how to do it?

Thank you in advance!

[v-jingzhang · Accepted solution]

Hi @omega123

I have made it! Please download the attachment to see detailed transformations. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[v-jingzhang]

Hi @omega123 

I found contract 5 has two incoming payments with different "Date to which the incoming payment refers", do you want to always aggregate them to the earliest "Date to which the incoming payment refers"?