Return a value based upon the latest date

[Joachimnicho]

Hi, 

I need help with an issue. 

I have a column with a project number [Projektnr], which has a delivery type [Leverancetype].

The [Projektnr] column has some duplicates. Whenever there is a duplicate, I need to return the [Leverancetype], which has the latest date [Datostempel]. 

I have tried using a variation of LOOKUPVALUE formulas, MAXX formulas etc., but cannot seem to make it work. 

I have posted a screenshot of the table to make the abovementioned more clear. 

Table

I would really appreciate, if anyone could help me with my issue!!

Regards

[v-chuncz-msft · Accepted solution]

@Joachimnicho,

You may refer to the following DAX that creates a new table.

Table =
FILTER (
    Table1,
    RANKX (
        CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[Projektnr] ) ),
        Table1[Datostempel],
        ,
        DESC,
        DENSE
    )
        <= 1
)

[Abduvali]

Hi @Joachimnicho,

Just try this:

Create new column: Latest Date = if(Sheet3[Date]=max(Sheet3[Date]),"Latest","")

and then just filter by "Latest"

Hope this helps.

Regards

Abduvali

[Joachimnicho]

The problem is, that I need to return the [Leverancetype] for all the [Projektnr], but take the [Projektnr] and corresponding [Leverancetype] with the latest date [Datostempel] if the [Projektnr] is duplicated. 

As a last thing. When this works out, I need to return the value [Leverancetype] in a different table, with a LOOKUPVALUE(Projektspec[Leverancetype]; 'Projektspec'[Projektnr];'Job Task'[Projektnr]), without it saying:

"A table of multiple values was supplied where a single value was expected"

So basically, I need to attach a filter to my LOOKUPVALUE, which makes it return the [Leverancetype] for all [Projektnr] and the [Leverancetype] with the latest date if the [Projektnr] is duplicated. 

Hope this made my situation more clear

Regards