Want Sum of sales in while getting from another table using lookup but only for maximum date.

[jay_patel]

Hello All ,

I am trying to get values from Table B to Table A using lookup functions using 'KEY'. Please have a look on both tables.

	table A
date	key
01-02-2021	AA
02-02-2021	AA
03-02-2021	AA
04-02-2021	BB
05-02-2021	CC
06-02-2021	DD
07-02-2021	DD
08-02-2021	EE
09-02-2021	FF
10-02-2021	FF

table B
key	Value
AA	100
BB	200
CC	200
DD	300
EE	150
FF	100

and I am trying to get values from table B to A. right now this is what i am getting,

	Current_Result
date	key	LookupValue
01-02-2021	AA	100
02-02-2021	AA	100
03-02-2021	AA	100
04-02-2021	BB	200
05-02-2021	CC	200
06-02-2021	DD	300
07-02-2021	DD	300
08-02-2021	EE	150
09-02-2021	FF	100
10-02-2021	FF	100

here it's getting for every row item for matching key but i only want for maximum date only. 

	Expected_Result
date	key	LookupValue
01-02-2021	AA
02-02-2021	AA
03-02-2021	AA	100
04-02-2021	BB	200
05-02-2021	CC	200
06-02-2021	DD
07-02-2021	DD	300
08-02-2021	EE	150
09-02-2021	FF
10-02-2021	FF	100

Thanks in Advance

[v-jayw-msft · Accepted solution]

Hi @jay_patel ,

Please check this formula.

Column = 
var _max = CALCULATE(MAX('Table A'[date]),FILTER('Table A','Table A'[key]=EARLIER('Table A'[key])))
var _value = CALCULATE(SUM('Table B'[Value]),FILTER('Table B','Table B'[key]='Table A'[key]))
return
IF('Table A'[date]=_max,_value,BLANK())

Best Regards,

Jay

[Tahreem24]

@jay_patel Try this DAX measure:

Measure =

VAR a_ = CALCULATE(MAX(Table1[date]),ALLEXCEPT(Table1,Table1[key]))

RETURN CALCULATE(SUM(Table2[Value]),FILTER(Table1,Table1[date]=a_))

[jay_patel]

It's not taking Column of Value.

[Shishir22]

Hello @jay_patel ,

Please use below DAX as calculated column -

LookupValue = 
Var A = Calculate(MAX('Table A'[date]),ALLEXCEPT('Table A','Table A'[key]))
Return
if('Table A'[date]=A,RELATED('Table B'[Value]),BLANK())

Please Accept it as solution if it solves your issue. Kudos are also appreciated.

Cheers,

Shishir

[jay_patel]

[Shishir22]

Give relationship between tableA and TableB on key column.

Cheers,

Shishir