Request for Assistance with Power BI Invoice Analysis Report

[stevesango]

Hello Power BI community,

I am currently developing a Power BI report focused on analyzing invoices for our organization. This report aims to provide insights into our outstanding invoices, overdue amounts, and overall payment status. The analysis will help us manage our receivables more effectively and understand cash flow.

Problem Statement

Currently, the report is displaying correct figures, but I need to refine the analysis further. Specifically, I need to ensure that the report does not display:

• Fully credited invoices: Invoices that have matching credit notes (where PayDeal is "CN" and CredInv references the original invoice) should be excluded from the report.
• Invoices with a total amount of 0.0 or blanks: Any invoices with these values should also not be displayed across all buckets of the report.

Understanding Credited Invoices

Credited invoices are defined based on the relationship between invoices and credit notes within our data structure. Here are the key fields in our Invoices table that help identify credited invoices:

• SerNr: This is the unique identifier for each invoice.
• PayDeal: This field indicates the type of transaction. If PayDeal is set to "CN," it signifies that the invoice is a credit note.
• CredInv: This field references the SerNr of the original invoice being credited. If an invoice has a CredInv value, it means that this invoice is linked to a credit note that reduces its amount due.

How Credited Invoices Work

1. Credit Note Generation: When a credit note is issued (i.e., PayDeal = "CN"), it serves as a reduction against a previously issued invoice. This means that the credit note effectively acknowledges that the customer will pay less than the originally invoiced amount due to various reasons (returns, discounts, errors, etc.).

2. Matching Amounts: To determine if an invoice should be excluded from the report, we compare the total amount of the original invoice (identified by SerNr) with the total amount of the corresponding credit note(s) (where CredInv matches the SerNr). If the amounts match, the original invoice is considered fully credited.

Below is an example of an example of the code for one of the Age buckets. See attached images for reference:

0-30 Days Age Bucket measure:

0-30 Days =

CALCULATE(

    SUMX(

        FILTER(

            MNF_INVOICES,

            TODAY() > MNF_INVOICES[PayDate] &&

            DATEDIFF(MNF_INVOICES[PayDate], TODAY(), DAY) <= 30 &&

            MNF_INVOICES[PayDeal] <> "CN"

        ),

        VAR InvoiceAmount = MNF_INVOICES[Sum4]

        VAR CreditedAmount =

            CALCULATE(

                SUM(MNF_INVOICES[Sum4]),

                FILTER(

                    MNF_INVOICES,

                    MNF_INVOICES[PayDeal] = "CN" &&

                    MNF_INVOICES[CreditInv] = EARLIER(MNF_INVOICES[SerNr])

                )

            )

        VAR PaidAmount =

            CALCULATE(

                SUM(MNF_RECEIPTS[RecVal]),

                FILTER(

                    MNF_RECEIPTS,

                    MNF_RECEIPTS[InvoiceNr] = MNF_INVOICES[SerNr]

                )

            )

        VAR RemainingAmount = InvoiceAmount - CreditedAmount - PaidAmount

        // Get exchange rate for non-USD invoices

        VAR CurrencyCode = MNF_INVOICES[CurncyCode]

        VAR ExchangeRate =

            IF(

                CurrencyCode <> "USD",

                MAXX(

                    TOPN(1,

                        FILTER(

                            MNF_EXRATES,

                            MNF_EXRATES[Date] <= MNF_INVOICES[TransDate]

                        ),

                        MNF_EXRATES[Date], DESC

                    ),

                    MNF_EXRATES[Rate2]

                ),

                1  // For USD, no conversion is needed

            )

        // Adjust the remaining amount for non-USD currencies

        VAR AdjustedRemainingAmount =

            IF(

                CurrencyCode <> "USD",

                RemainingAmount / ExchangeRate,

                RemainingAmount

            )

        RETURN

        AdjustedRemainingAmount

        // IF(

        //     RemainingAmount <= 0,

        //     BLANK(),  // Exclude fully credited or fully paid invoices

        //     AdjustedRemainingAmount  // Show the adjusted balance for non-USD invoices

        // )

    )

)

I have the same logic applied fr all the age buckets such as 31-60 Days, 61-90 Days and 91 + Days

Request for Help

I would appreciate any guidance on how to implement these filters in my Power BI report to ensure that only relevant invoices are displayed.

Thank you for your assistance!

[v-yajiewan-msft]

Hi @stevesango ,

Add the following logic in your measure to compare the result:

VAR IsFullyCredited = InvoiceAmount = CreditedAmount

RETURN
IF(
    IsFullyCredited || AdjustedRemainingAmount <= 0,
    BLANK(), 
    AdjustedRemainingAmount  
)

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sangosteve]

I tried but its still showing the invoices. F.Y.I when calculating its actually ignoring fully credited invoices as intented. My problem is its still displaying the fully credited invoices.