How Get a list of invoices with credit lines

[gomezc73]

Hi,

 I have a table that have a list of invoices with different lines, but sometimes there are invoices that have a the credit line(negative amount) and it has a 'Due date' different to the rest of lines.. The user have to check invoices by invoices to detect the invoices with that issue to correct the due date in that line.

This is an example of the data:

Invoice	Line	GL Date	Due Date	Amount
A01	1	10/1/2023	10/15/2023	100
A01	2	10/1/2023	10/2/2023	-12
A02	1	10/1/2023	10/30/2023	250
A03	1	10/1/2023	10/15/2023	800
A03	2	10/1/2023	10/15/2023	200
A03	3	10/1/2023	10/15/2023	200
A04	1	10/5/2023	10/15/2023	1200
A04	2	10/5/2023	10/6/2023	-200
A05	1	10/1/2023	10/15/2023	350
A06	1	10/1/2023	10/15/2023	200
A06	2	10/1/2023	10/15/2023	230
A06	3	10/1/2023	10/15/2023	-50
A06	4	10/1/2023	10/15/2023	800

In this example the formula needed must generate a report like this:

Invoices with Credit lines and distint Due Date
A01
A04

The invoice A06 has a negative line, but all lines in that invoice is the same (10/15/2023), for that reason it must be ommited.

I reaaly appreciate your help

[parry2k · Accepted solution]

@gomezc73 try this measure:

Credit Lines = 
VAR __Rawable = 
ADDCOLUMNS ( 
    SUMMARIZE ( 
        Invoices,
        Invoices[Invoice] 
    ), 
    "@DueDateCount", CALCULATE ( DISTINCTCOUNT ( Invoices[Due Date] ), ALLEXCEPT ( Invoices, Invoices[Invoice] ) ), 
    "@CreditAmount", CALCULATE ( SUM ( Invoices[Amount] ), Invoices[Amount] < 0 ) 
)
VAR __CreditInvoices = FILTER ( __Rawable, [@DueDateCount] > 1 && NOT ISBLANK ( [@CreditAmount] ) )
RETURN

SUMX ( __CreditInvoices, [@CreditAmount] )

Here is the output:

[parry2k]

ofcourse everything is easy 👍

[parry2k]

@ThxAlot Great solution but will not work as soon as you add another measure or column in the visual. It will only work if you have invoice column in the visual, simple but not complete 🙂

[ThxAlot]

Easy to amend.

[ThxAlot]

Due Inv.pbix

Simple enough

[gomezc73 · Accepted solution]

Guaoo, this measure also works perfect!!. thank you!!

[parry2k · Accepted solution]

@gomezc73 try this measure:

Credit Lines = 
VAR __Rawable = 
ADDCOLUMNS ( 
    SUMMARIZE ( 
        Invoices,
        Invoices[Invoice] 
    ), 
    "@DueDateCount", CALCULATE ( DISTINCTCOUNT ( Invoices[Due Date] ), ALLEXCEPT ( Invoices, Invoices[Invoice] ) ), 
    "@CreditAmount", CALCULATE ( SUM ( Invoices[Amount] ), Invoices[Amount] < 0 ) 
)
VAR __CreditInvoices = FILTER ( __Rawable, [@DueDateCount] > 1 && NOT ISBLANK ( [@CreditAmount] ) )
RETURN

SUMX ( __CreditInvoices, [@CreditAmount] )

Here is the output:

[gomezc73]

Amazing!! You're the best, works perfect!! thank you so much.