Forum Discussion

Joachimnicho's avatar
Joachimnicho
Frequent Visitor
8 years ago

Return a value based on the latest date

Hi,

 

I have an issue. 

 

I am trying to return a value, which is text, based on a date corresponding to the same row, but in a different column.

The problem is, that I have som duplicate values in the column, where I wish to return the value.

 

So I am trying to build a formula, which returns a text value based on the latest date. 

 

I have tried multiple variations of LOOKUPVALUE combined with LASTDATE and MAX, but it keeps saying that "A table of multiple values was supplied where a single value was expected"

 

Any help is appreciated, since I am new to Power Bi. :-)  

8 Replies

  • Joachimnicho's avatar
    Joachimnicho
    Frequent Visitor
    8 years ago

    Hi,

     

    I have an issue.

     

    I have a column, which is text. 

     

    Problem is I have duplicate values, but with different starting dates. 

     

    How can I set up a formula, which returns the value(text) starting at the latest date?

     

    I have tried functions such as: LASTDATE and MAX, but cannot seem to make it work. 

     

    Any help is appreciated, since I am new to Power Bi! :-) 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Dear Joachimnicho

       

      Can you post your fomular here?

       

      Regards.

      • Joachimnicho's avatar
        Joachimnicho
        Frequent Visitor
        8 years ago

        Hi again

         

        I have tried this formula:

         

        Leverancetype = LOOKUPVALUE(ProjektSpec[Leverancetype];ProjektSpec[Projektnr];'Job Task'[Job No_])&LASTDATE(ProjektSpec[Datostempel].[Date])

         

        Trying to return [Leverancetype) by holding the project number from the table with the [Leverancetype] equal to the project number from the table I wish to post in. Secondly I need it to take the [Leverancetype] based on the latest date. But due to multiple values in the column [Leverancetype] being the same it doesn't work. That is why I need the date filtering.

         

        I hope this made my situation clearer!

    • bsas's avatar
      bsas
      Icon for Post Patron rankPost Patron
      8 years ago

      Hi Joachimnicho,

       

      Could you please draw your table and table-result that you want to receive.

      • Joachimnicho's avatar
        Joachimnicho
        Frequent Visitor
        8 years ago

        Hi again,

         

        This is a picture of the table. 

         

        I wish to make a new column in a different table, where it returns the [Leverancetype] with the latest date [Datostempel].

        I set the function to hold [Projektnr] from this table equal to [Job no] in the table I wish to post the new column with the [Leverancetype]. My formula looks like the following, but does not work unfortunately:

         

        Leverancetype = LOOKUPVALUE(ProjektSpec[Leverancetype];ProjektSpec[Projektnr];'Job Task'[Job No_])&LASTDATE(ProjektSpec[Datostempel].[Date])

         

         

         

        I hope this helps and makes the situation more clear! 

         

        Thank you for your time! 

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago
    Joachimnicho wrote:

    Hi,

     

    I have an issue. 

     

    I am trying to return a value, which is text, based on a date corresponding to the same row, but in a different column.

    The problem is, that I have som duplicate values in the column, where I wish to return the value.

     

    So I am trying to build a formula, which returns a text value based on the latest date. 

     

    I have tried multiple variations of LOOKUPVALUE combined with LASTDATE and MAX, but it keeps saying that "A table of multiple values was supplied where a single value was expected"

     

    Any help is appreciated, since I am new to Power Bi. :-)  

    Joachimnicho

    You can see a simple demo as below.

    return value =
 ( CALCULATE ( MAX ( Sheet[date] ), ALLEXCEPT ( Sheet, Sheet[value] ) ) )
return =
MAXX ( FILTER ( Sheet, Sheet[date] = Sheet[lasted date] ), Sheet[value] )

    • Joachimnicho's avatar
      Joachimnicho
      Frequent Visitor
      8 years ago

      Thank you for your time!

       

      But I need to hold the [projektnummer] in the table where I want to extract the value [Leverancetype] equal to [Job_no] in the table I am going to post the value. How do I apply that to formula?

    • Joachimnicho's avatar
      Joachimnicho
      Frequent Visitor
      8 years ago
      Eric_Zhang wrote:
      Joachimnicho wrote:

      Hi,

       

      I have an issue. 

       

      I am trying to return a value, which is text, based on a date corresponding to the same row, but in a different column.

      The problem is, that I have som duplicate values in the column, where I wish to return the value.

       

      So I am trying to build a formula, which returns a text value based on the latest date. 

       

      I have tried multiple variations of LOOKUPVALUE combined with LASTDATE and MAX, but it keeps saying that "A table of multiple values was supplied where a single value was expected"

       

      Any help is appreciated, since I am new to Power Bi. :-)  

      Joachimnicho

      You can see a simple demo as below.

      return value =
 ( CALCULATE ( MAX ( Sheet[date] ), ALLEXCEPT ( Sheet, Sheet[value] ) ) )
return =
MAXX ( FILTER ( Sheet, Sheet[date] = Sheet[lasted date] ), Sheet[value] )

       

      Eric_Zhang wrote:
      Joachimnicho wrote:

      Hi,

       

      I have an issue. 

       

      I am trying to return a value, which is text, based on a date corresponding to the same row, but in a different column.

      The problem is, that I have som duplicate values in the column, where I wish to return the value.

       

      So I am trying to build a formula, which returns a text value based on the latest date. 

       

      I have tried multiple variations of LOOKUPVALUE combined with LASTDATE and MAX, but it keeps saying that "A table of multiple values was supplied where a single value was expected"

       

      Any help is appreciated, since I am new to Power Bi. :-)  

      Joachimnicho

      You can see a simple demo as below.

      return value =
 ( CALCULATE ( MAX ( Sheet[date] ), ALLEXCEPT ( Sheet, Sheet[value] ) ) )
return =
MAXX ( FILTER ( Sheet, Sheet[date] = Sheet[lasted date] ), Sheet[value] )

       

      I have tried to apply your formula. 

       

      Firstly, the "return value" formula returns a date - not a value? 

      Secondly, the "return" formula only returns the last value in the value column. Meaning that the same value is returned in the entire column. 

       

      Can you explain this? 

       

      Thanks!