Return Max Date

[Euro0681]

The Table on the left is all of my data, meaning if i Drag and drop all Columns into a Table in Power BI I will see the Table on the left.
The problem is i'm wanting to see the Table Displayed on the right I can't simply use a filter since I want to see both 'Delivered' and 'In Progress' Without duplicates and for the duplicates just show me the customer with MAX Date between the 2.
I've tried implementing a logic to return max date if value repeats?....

Calculated Column 1 = Concatenate( 'Table'[ID], Concatenate( " ",'Table'[Name] ) ) <---- To give me a Unique Value
Calculated Column 2 = If ( Count ('Table'[Calculate Column 1] > 1, Max ( 'Table'[Order Date] ), 'Table'[Order Date] ) <-- Problem

I then wanted to use Calculated Column 2 instead of [Order Date] so I could display the table on the right (since all values for repeating customers would be the same removing the duplicates and only showing the most recent values), But for 
Calculated Column 2 It's returning me the MAX for the entire order date column which is '6/13/2023' 
so if anybody has any suggestions to return the MAX date for duplicates but the MAX date should only be the MAX between the duplicates (Example Below)? 

[v-yadongf-msft · Accepted solution]

Hi @Euro0681 ,

Create a new column:

Max_date = MAXX(FILTER('Table','Table'[ID] = EARLIER('Table'[ID])),[Order Date])

Your expected output2:

Create a measure:

Measure = 
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( 'Table', 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) )
    )
VAR max_date =
    MAXX (
        FILTER ( 'Table', 'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] ) ),
        [Order Date]
    )
VAR _status =
    IF (
        _count > 1
            && MAX ( 'Table'[Order Date] ) = max_date,
        MAX ( 'Table'[Delivered/In progess] ),
        MAX ( 'Table'[Delivered/In progess] )
    )
RETURN
    IF ( ISINSCOPE ( 'Table'[ID] ), _status, BLANK () )

Your expected output1:

Best regards,

Yadong Fang

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

[amitchandak]

@Euro0681 ,

Try a new column like

maxx(filter(Table, [ID] = earlier([ID]) ), [Order Date])