cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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  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
2 REPLIES 2
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.

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.

Frequent Visitor

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors