Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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 accounts with the earliest due dates and according to the priority of the incoming payment (see 2 tables below).
@v-jingzhang has already helped me to find a solution how to assign incoming payments to the accounts with the earliest due dates. Here is the .pbix file https://drive.google.com/file/d/1nbcGl0DdIWeqJUxzzvFsQEybTS4uYU2_/view?usp=share_link. I would like to find out now, how to take into account the priority of the payment, where 1 is a top priority.
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.
What steps should be added to the code?
I would be very grateful for your tips.
INITIAL TABLE
Date | Contract nmb | Amount payable | Payment deadline | Received payment | Priority |
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 | 200 | 4 | ||
26.02.2022 | 1 | 100 | 2 | ||
27.02.2022 | 1 | 50 | 1 | ||
28.02.2022 | 3 | 150 | 1 | ||
01.03.2022 | 4 | 350 | 5 | ||
02.03.2022 | 5 | 50 | 1 | ||
03.03.2022 | 5 | 50 | 3 |
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 | 150 | 50 |
05.02.2022 | 4 | 400 | 19.02.2022 | 350 | 50 |
08.02.2022 | 2 | 300 | 22.02.2022 | 200 | 100 |
10.02.2022 | 5 | 100 | 24.02.2022 | 100 | 0 |
13.02.2022 | 1 | 500 | 07.03.2022 | 50 | 450 |
14.02.2022 | 1 | 400 | 10.03.2022 | 400 | |
16.02.2022 | 3 | 100 | 17.03.2022 | 100 | |
17.02.2022 | 5 | 800 | 20.03.2022 | 800 | |
19.02.2022 | 2 | 200 | 25.03.2022 | 200 |
Hi @omega123 ,
After my research and test, I doubt it's possible.
I'm having trouble here. There doesn't seem to be a dynamic way to automatically match the most recent date by priority.
Forgive me, I tried my best.😫
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Stephen @Anonymous
Thank you very much for your help!
Do you know if it is possible to solve this problem with VBA/macros?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
9 | |
8 | |
7 | |
7 |