Max Date filtered by a specific criteria/ID

[Philipp_ISARTAX]

Hello all,

I'va a problem receiving a result for a date. Here is the table-strucutre:

Table Name 'Invoices':

Invoice-Date	Product	Purchase Order (PO)	Latest Invoice-Date for PO
01.12.2023	X	A
05.12.2023	X	B
06.01.2024	Y	A
08.05.2022	Z	C
01.12.2023	X	B
08.09.2023	Z	A

My DAX-Try for the Column Latest Invoice Date for PO was as follows:

Latest Invoice-Date for PO = CALCULATE(Max('Invoices' [Invoice-Date]);Filter('Invoices';'Invoices'[Purchase Order (PO)]='Invoices'[Purchase Order (PO)]))

The Dax provides the following Values:

Invoice-Date	Product	Purchase Order (PO)	Latest Invoice-Date for PO
01.12.2023	X	A	06.01.2024
05.12.2023	X	B	06.01.2024
06.01.2024	Y	A	06.01.2024
08.05.2022	Z	C	06.01.2024
01.12.2023	X	B	06.01.2024
08.09.2023	Z	A	06.01.2024

I would expect the following Values:

Invoice-Date	Product	Purchase Order (PO)	Latest Invoice-Date for PO
01.12.2023	X	A	06.01.2024
05.12.2023	X	B	05.12.2023
06.01.2024	Y	A	06.01.2024
08.05.2022	Z	C	08.05.2022
01.12.2023	X	B	05.12.2023
08.09.2023	Z	A	06.01.2024

Any Idea, how to adjust the Dax formula to receive the expected values?

Kind regards

[some_bih · Accepted solution]

Hi @Philipp_ISARTAX possible solution, create measure as following

LatestInvoiceDate =

CALCULATE(

    MAX('Invoices_new'[Invoice_Date]),

    ALLEXCEPT('Invoices_new','Invoices_new'[PurchaseOrdrDate])

)

Adjust column and table names,