Skip to main content
cancel
Showing results for 
Search instead 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

Reply
gomezc73
Helper IV
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:

InvoiceLineGL DateDue DateAmount
A01110/1/202310/15/2023100
A01210/1/202310/2/2023-12
A02110/1/202310/30/2023250
A03110/1/202310/15/2023800
A03210/1/202310/15/2023200
A03310/1/202310/15/2023200
A04110/5/202310/15/20231200
A04210/5/202310/6/2023-200
A05110/1/202310/15/2023350
A06110/1/202310/15/2023200
A06210/1/202310/15/2023230
A06310/1/202310/15/2023-50
A06410/1/202310/15/2023800

 

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

 

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@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_0-1696548251623.png

 

 



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.

View solution in original post

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

View solution in original post

7 REPLIES 7
parry2k
Super User
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.

parry2k
Super User
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.

Easy to amend.



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



ThxAlot
Super User
Super User

Due Inv.pbix

 

Simple enough

ThxAlot_0-1696558041972.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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

parry2k
Super User
Super User

@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_0-1696548251623.png

 

 



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.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors