Get the latest value of table 1 to table 2 with parameteres/condtions

[Maricon]

Hi, Can you help me solve my issue- what measure or calculated column should I use. I wanted to input the latest price from Table 2 to Table 1, but need to make sure its the same group, same item code. See my sample, table 1 column price, thats the value I wanted to show in my measure or column. Thanks you in advance

[v-lionel-msft]

Hi @Maricon ,

You just need to import 'Table 2' and creates a calculated table(Please refer to my .pbix file.).

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Maricon , Create a new column in Table 1

lastest price = maxx(filter(Table2,Table2[item code] =Table1[item code] && Table2[Group] =Table1[Group]),lastnonblankvalue(Table1[pricedate],max(price)))

[Maricon]

@amitchandakThanks, Maybe I didnt explained well. 

The price should be the latest for each year. If today is 2020, latest price for 2020, and if no price for 2020, show the last price before the order date. Thanks again in advance

[amitchandak]

@Maricon , last nonblank will take latest data from 2020 if present, if not then from 2019, That should work. If not.

Can you share sample data and sample output in table format?

[Maricon]

@amitchandak @v-lionel-msft  thanks for checking-- i tride both your solution and still not showing what I want. 

Let me change the data sample--see below.

In below table, Both your solution resulted to same value,  Price V1 - I used @amitchandak recommended solution and Price V2 I used @v-lionel-msft recommended solution.

This is what I wanted to see :

All 2018 dates should be 3,047.63 , 2019 dates should be 2585.38 & 2020 date shld be 2461. Hope you can help me. Thanks in advance.

here below are my tables

Order Date	Item	Group
16/04/2018 0:00	123	AAA
18/04/2018 0:00	123	AAA
04/07/2018 0:00	123	AAA
06/08/2018 0:00	123	AAA
30/08/2018 0:00	123	AAA
17/01/2019 0:00	123	AAA
25/01/2019 0:00	123	AAA
01/02/2019 0:00	123	AAA
21/05/2019 0:00	123	AAA
22/08/2019 0:00	123	AAA
29/08/2019 0:00	123	AAA
29/10/2019 0:00	123	AAA
06/11/2019 0:00	123	AAA
19/02/2020 0:00	123	AAA
23/03/2020 0:00	123	AAA

Group	Price Date	Item	Price
AAA	13/01/2014 9:32	123	3487.77
AAA	03/10/2014 8:37	123	3427.272
AAA	06/10/2015 11:14	123	3282.222
AAA	10/03/2016 3:48	123	3038.08
AAA	05/10/2016 14:33	123	4633.88
AAA	12/01/2018 3:59	123	3047.625
AAA	30/01/2019 12:06	123	2585.375
AAA	01/02/2020 8:33	123	2461

[v-lionel-msft · Accepted solution]

Hi @Maricon ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.