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

Helper IV

## How Get a list of invoices with credit lines

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.

2 ACCEPTED SOLUTIONS
Super User

@gomezc73 try this measure:

``````Credit Lines =
VAR __Rawable =
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:

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper IV

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

7 REPLIES 7
Super User

ofcourse everything is easy 👍

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@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 🙂

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

Easy to amend.

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

Due Inv.pbix

Simple enough

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Helper IV

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

Super User

@gomezc73 try this measure:

``````Credit Lines =
VAR __Rawable =
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:

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper IV

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

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.