Assistance with Maturity Analysis: Analyzing Past Due Payments by Specific Date

[digicontrolling]

Hello Power BI community,

I'm working on a complex problem related to maturity analysis, specifically categorizing due payments by various age ranges (0-30 days, 31-60 days, over 60 days) as of a specific date. The goal is to retrospectively analyze the status of due payments as of a specific date, even if the payment has been made since. The goal is to see how the different categories evolve over time.

Challenges:

1. Analyzing Past Payments: If an invoice was issued on 15th March and paid on 14th May, and I want to analyze data as of 31st March, the invoice should appear in the "0-30 days due" category, even though it was paid later.

2. Partial Payments: Some customers may have paid partial amounts, which must be considered in the analysis.

Previous Attempts:

1. Direct Categorization in Data Source: Tried to create the due categories directly in the data source without the ability to retrospectively view the status on a specific date.

2. Calculated Column using DAX: Used DAX functions like VAR, FILTER, MAXX, SUMX, but couldn't correctly classify the due status by the specific date, nor accommodate specific conditions like partial payments.

3. Utilizing Power Query: Attempting to transform data before modeling in Power BI didn't yield desired results.

4. Separate Date Table Creation: Made a separate date table for historical analysis, but it led to inconsistencies in the calculated amounts.

5. Considering Partial Payments: Attempts to account for partial payments led to difficulties in the correct assignment of outstanding amounts.

6. Combination of Filters and Context Control: Various combinations of filters, context control, and logical conditions didn't provide the expected accuracy and consistency.

The complexity of requirements and specific data structure have made previous attempts unsuccessful. The retrospective analysis of due status on a given date remains a unique challenge.

I would greatly appreciate any insights or guidance from the community to tackle this problem. Here's a glimpse of the data structure:

Paid Example

Unpaid

Thank you in advance for your assistance! If it is possible I could provide a pbix with sample data.

The current classification which works for the current due category classification is done with the following calculated column:

DueCategory =

VAR InvoiceNumber = 'SalesLineItems'[InvoiceNumber]

VAR MaxDebitDate = MAXX(FILTER('SalesLineItems', 'SalesLineItems'[InvoiceNumber] = InvoiceNumber), 'SalesLineItems'[DebitDate])

VAR InvoiceCurrency = MAXX(FILTER('SalesLineItems', 'SalesLineItems'[InvoiceNumber] = InvoiceNumber && NOT(ISBLANK('SalesLineItems'[PosAmount]))), 'SalesLineItems'[Currency])

VAR PosAmount = SUMX(FILTER('SalesLineItems', 'SalesLineItems'[InvoiceNumber] = InvoiceNumber && 'SalesLineItems'[Currency] = InvoiceCurrency), 'SalesLineItems'[PosAmount])

VAR TotalPayment = SUMX(FILTER('SalesLineItems', 'SalesLineItems'[InvoiceNumber] = InvoiceNumber && 'SalesLineItems'[PaymentCurrency] = InvoiceCurrency), 'SalesLineItems'[PaymentAmount])

RETURN

    IF(

        ISBLANK(TotalPayment) || ROUND(TotalPayment,1) < ROUND(PosAmount,1),

        VAR DaysDue = TODAY() - MaxDebitDate

        RETURN

            SWITCH(

                TRUE(),

                DaysDue-30 <= 30, "0-30 days due",

                DaysDue-30 <= 60, "31-60 days due",

                "over 60 days due"

            ),

        "Already paid"

    )