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 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

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
)

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    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