Create grouped table from date field

[brief001]

I want to create a table from another table. This table must group the records whose unique record must be preserved based on a date field. In the table you see John in double, my result should be that John of team Sweden is kept, because that date has the highest value.

Name	Team	Date
John	Team Polen	01-01-2019
John	Team Sweden	01-01-2021
Fred	Team Holland	01-01-2020
Jack	Team Belgium	01-01-2020
Steve	Team Germany	01-01-2021

The DAX formula for creating a new calculated table must return the result below. (The John from Team Sweden must stay, because the date field of John from Team Poland was before the date of John from Team Sweden).

Name	Team	Date
John	Team Sweden	01-01-2021
Fred	Team Holland	01-01-2020
Jack	Team Belgium	01-01-2020
Steve	Team Germany	01-01-2021

Thanks in advance for the help!

[mahoneypat · Accepted solution]

Please use this table expression.

NewTable =
ADDCOLUMNS (
    DISTINCT ( Team[Name] ),
    "Date",
        CALCULATE (
            LASTNONBLANK (
                Team[Date],
                MIN ( Team[Date] )
            )
        ),
    "Team",
        CALCULATE (
            LASTNONBLANKVALUE (
                Team[Date],
                MIN ( Team[Team] )
            )
        )
)

Pat

[wdx223_Daniel · Accepted solution]

Table=FILTER(SampleData,SampleData[Date]=CALCULATE(MAX(SampleData[Date]),ALLEXCEPT(SampleData,SampleData[Name])))

[brief001]

Thank you for also posting this answer. The formula works differently from the answer given earlier. But the result is the same. And I also understand this formula now that I read it.

[mahoneypat · Accepted solution]

Please use this table expression.

NewTable =
ADDCOLUMNS (
    DISTINCT ( Team[Name] ),
    "Date",
        CALCULATE (
            LASTNONBLANK (
                Team[Date],
                MIN ( Team[Date] )
            )
        ),
    "Team",
        CALCULATE (
            LASTNONBLANKVALUE (
                Team[Date],
                MIN ( Team[Team] )
            )
        )
)

Pat

[brief001]

Thank you very much for your quick response. I've tested your formula and it works. And personally beautiful, I understand your formula too.