Forum Discussion

Joachimnicho's avatar
Joachimnicho
Frequent Visitor
8 years ago
Solved

Return a value based upon the latest date

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...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    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's avatar
Abduvali
Skilled Sharer
8 years ago

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's avatar
    Joachimnicho
    Frequent Visitor
    8 years ago

    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