Summarizing Accounts Receivable Data to show Net Balance Value per Invoice ID

[JoZu]

Hi everyone

First of all I'd like to mention, that I am rather new to PowerBI and I am reading a LOT (!) in this forum. The posts are very helpful and solved already numerous issues I faced. However, there is one problem I could not solve yet:

We are creating a accounts receivable dashboard for our sales employees, where they should see the open and due invoices of their customers. This is what it looks like (recreated example with sample data - due to confidentiality of the data):

The Open Invoices Table shows all unpaid Invoices. The Due Invoices Table shows all open invoices with a due date which is in the past. 

The problem:

Our customers are allowed to make partial payments of an invoice. Those cases appear as negative numbers in the table and do not have a due date. This is a problem, as my sum for the due invoices is not correct.

My goal:

I wanted to create a measure which summarizes the "Balance in Transaction Currency" based on the "Customer Invoice ID".  This is what I wrote as a measure: 

Net Value per Invoice =

SUMX (

    SUMMARIZE (

        'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID],

        "Net Balance", SUM('Accounts Receivable'[Balance in Transaction Currency])

    ),

    [Net Balance]

)

Unfortunately this does not solve my issue. However, if I only use the Summarize function and create a new table with it, it works fine. But I want to avoid an additional Table in our dataset, as it is already pretty big and hard to see all the relationships.

Sample data: 

The table is called "Accounts Receivable" in our data model.

Business Partner ID (Account ID)	Customer Invoice ID	Due Date	Due Date Calendar Date	Transaction Currency	Balance in Transaction Currency
1001111	10	1.65698E+12	07.07.2022	EUR	100
1001111	20	1.65698E+12	07.07.2022	EUR	250
1001111	30	1.65698E+12	07.07.2022	EUR	500
1001111	30			EUR	-100
1001122	40	1.65698E+12	07.07.2022	EUR	100
1001122	50	1.65698E+12	07.07.2022	EUR	500
1001133	60	1.65698E+12	07.07.2022	EUR	1000
1001133	60			EUR	-500
1001133	70	1.65698E+12	07.07.2022	EUR	300
1001133	70			EUR	-50
1001133	80	1.65698E+12	07.07.2022	EUR	200

Note: Due Date is in epoch format, Due Date Calendar Date is formatted as a "normal" date.

The entirely open Invoices are not causing any issues. The one I am interested in are the ones with partial payments (you can identify them by Customer Invoice ID appearing multiple times and a missing due date). The expected results for the due invoices tables are (cases with partial payments are marked red): 

Business Partner ID (Account ID)	Customer Invoice ID	Due Date	Due Date Calendar Date	Transaction Currency	Balance in Transaction Currency
1001111	10	1.65698E+12	07.07.2022	EUR	100
1001111	20	1.65698E+12	07.07.2022	EUR	250
1001111	30	1.65698E+12	07.07.2022	EUR	400
1001122	40	1.65698E+12	07.07.2022	EUR	100
1001122	50	1.65698E+12	07.07.2022	EUR	500
1001133	60	1.65698E+12	07.07.2022	EUR	500
1001133	70	1.65698E+12	07.07.2022	EUR	250

I'd be very grateful if someone of this great community could help me solve this issue. Many thanks already in advance!

[JoZu]

Thank you very much @tamerj1 ! The measure works perfectly fine and does what its supposed to! I forgot to mention, that the date restriction was done with a filter on the visual. You even included this into the measure - even better.

I am more than happy with this solution and the very fast reply - THANKS!

[tamerj1 · Accepted solution]

HI @JoZu 
Here is a sample file with the solution https://we.tl/t-BJ0BtLRpwW

Due Transation Currency = 
SUMX ( 
    SUMMARIZE ( 'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID],'Accounts Receivable'[Due Date Calendar Date] ),
    CALCULATE (
        VAR CurrentDueDate = SELECTEDVALUE ( 'Accounts Receivable'[Due Date Calendar Date] )
        RETURN 
            IF (
                CurrentDueDate <= TODAY ( ) && CurrentDueDate <> BLANK ( ),
                CALCULATE ( 
                    SUM ( 'Accounts Receivable'[Balance in Transaction Currency] ), 
                    ALLEXCEPT ( 'Accounts Receivable', 'Accounts Receivable'[Customer Invoice ID] ) 
                )
            )
    )
)