Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
omega123
Frequent Visitor

Aggregate table

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

DateContract nmbAmount payablePayment deadlineReceived paymentPriority
01.02.2022     
02.02.2022110015.02.2022  
03.02.2022320015.02.2022  
04.02.2022     
05.02.2022440019.02.2022  
06.02.2022     
07.02.2022     
08.02.2022230022.02.2022  
09.02.2022     
10.02.2022510024.02.2022  
11.02.2022     
12.02.2022     
13.02.2022150007.03.2022  
14.02.2022140010.03.2022  
15.02.2022     
16.02.2022310017.03.2022  
17.02.2022580020.03.2022  
18.02.2022     
19.02.2022220025.03.2022  
20.02.2022     
21.02.2022     
22.02.2022     
23.02.2022     
24.02.2022     
25.02.20222  2004
26.02.20221  1002
27.02.20221  501
28.02.20223  1501
01.03.20224  3505
02.03.20225  501
03.03.20225  503

 

AGGREGATE TABLE

DateContract nmbAmount payablePayment deadlineReceived paymentRemaining amount payable
02.02.2022110015.02.20221000
03.02.2022320015.02.202215050
05.02.2022440019.02.202235050
08.02.2022230022.02.2022200100
10.02.2022510024.02.20221000
13.02.2022150007.03.202250450
14.02.2022140010.03.2022 400
16.02.2022310017.03.2022 100
17.02.2022580020.03.2022 800
19.02.2022220025.03.2022 200
2 REPLIES 2
v-stephen-msft
Community Support
Community Support

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.

vstephenmsft_0-1669873542461.png

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 @v-stephen-msft 

Thank you very much for your help!

Do you know if it is possible to solve this problem with VBA/macros?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors