Return a value based on the latest date

[Joachimnicho]

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

[Eric_Zhang]

---

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

---

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

[Joachimnicho]

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]

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

[bsas]

Hi @Joachimnicho,

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

[Joachimnicho]

---

---

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! 

[Anonymous]

Dear @Joachimnicho

Can you post your fomular here?

Regards.

[Joachimnicho]

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!