MIN and MAX with grouping in another column.

[ToddMate]

Hi,

I am trying to determine the MIN invoice number for each customer (column A) and update the "First Invoice" column with "First" where the MIN invoice number is identified.

Same thing for MAX, except it updates the "Last Invoice" column with "Final"

I've done a mock up in excel to demonstrate what i am trying to achieve (The column names are the same in Power BI)

Is anyone able to assist with this?

Cheers
Todd.

[vik0810 · Accepted solution]

Your DAX Formula for Column Last Invoice would be

Last Invoice =
VAR CustNo =
    VALUES ( table[Customer Number] )
VAR InvoiceNo =
    VALUES ( table[Invoice Number] )
VAR MaxInvoiceNo =
    CALCULATE (
        MAX ( table[Invoice Number] ),
        ALL ( table ),
        table[Customer Number] = CustNo
    )
RETURN
    IF ( InvoiceNo = MaxInvoiceNo, "Final" )

The formula for first invoice would be analog, just change max for min and "final" for "first"