Highest outstanding per customer in a period

[LotteLaugesen]

Hi

I need to find the highest outstanding per customer in a given period.

In my table I have invoices and payments and of course date, customer number and amount.

Do you have any suggestions?

[johnt75 · Accepted solution]

If you create a measure like

Outstanding Amount =
VAR maxDate =
    MAX ( 'Date'[Date] )
VAR invoicedAmount =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        REMOVEFILTERS ( 'Date' ),
        ALLEXCEPT ( 'Table', 'Table'[Customer No] ),
        'Table'[Date] <= maxDate,
        'Table'[Type] = "Invoice"
    )
VAR paidAmount =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        REMOVEFILTERS ( 'Date' ),
        ALLEXCEPT ( 'Table', 'Table'[Customer No] ),
        'Table'[Date] <= maxDate,
        'Table'[Type] = "Payment"
    )
RETURN
    invoicedAmount - paidAmount

then your max outstanding amount would be

Max Outstanding = MAXX( VALUES('Date'[Date]), [Outstanding Amount])

[LotteLaugesen]

Thank you @johnt75 - it works perfect 👍

[LotteLaugesen]

Example 😉

So if the filtered period is 1/3/2022 - 31/3/2022 it should show row 4 and 9