Filter table with calculated table

[Applicable88]

Hello, 

my original table:

Category		Date		Name
A		25.01.2022		Pan
B		25.01.2022		Hook
A		24.01.2022		Hook
B		24.01.2022		Pan
A		23.01.2022		Pan
B		23.01.2022		Hook

How to make a calculated table which in any case always only shows the newest date for both categories ? 

Expected outcome like this:

Category		Date		Name
A		25.01.2022		Pan
B		25.01.2022		Hook

I tried addcolumns with summarize and calculated max for date and name. It might works for date but somehow there is no context tranisition for the name. Instead it will show the name with the "higher" alphabet.  The name can be wrong. 

Thank you very much in advance.

Best. 

[tamerj1 · Accepted solution]

Hi @Applicable88 
For table you can use

Last Data = 
VAR DataWithLastDatePerCategory =
    ADDCOLUMNS ( 
        Data,
        "@LastDate",
        VAR CategoryTable =
            CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
        RETURN
            MAXX ( 
                CategoryTable,
                Data[Date]
            )
    )
VAR Result =
    FILTER ( DataWithLastDatePerCategory, Data[Date] = [@LastDate] )
RETURN
    SELECTCOLUMNS ( Result, "Category", [Category ], "Date", [Date], "Name", [Name] )

for Calculated column you can use

Name On Last Date = 
VAR CategoryTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
VAR MaxDate =
    MAXX ( 
        CategoryTable,
        Data[Date]
    )
VAR Result =
    MAXX (
        FILTER ( CategoryTable, Data[Date] = MaxDate ),
        Data[Name]
    )
RETURN
    Result 

[BeaBF · Accepted solution]

@Applicable88 Try with:

= FILTER (

Tabella,

Tabella[Date]

= CALCULATE (

MAX ( Tabella[Date] ),

ALLEXCEPT ( Tabella, Tabella[Category ] )

)

)

B.

[tamerj1 · Accepted solution]

Hi @Applicable88 
For table you can use

Last Data = 
VAR DataWithLastDatePerCategory =
    ADDCOLUMNS ( 
        Data,
        "@LastDate",
        VAR CategoryTable =
            CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
        RETURN
            MAXX ( 
                CategoryTable,
                Data[Date]
            )
    )
VAR Result =
    FILTER ( DataWithLastDatePerCategory, Data[Date] = [@LastDate] )
RETURN
    SELECTCOLUMNS ( Result, "Category", [Category ], "Date", [Date], "Name", [Name] )

for Calculated column you can use

Name On Last Date = 
VAR CategoryTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
VAR MaxDate =
    MAXX ( 
        CategoryTable,
        Data[Date]
    )
VAR Result =
    MAXX (
        FILTER ( CategoryTable, Data[Date] = MaxDate ),
        Data[Name]
    )
RETURN
    Result