sumx product across two tables

[alsm]

I have two tables with following relationship

I have enhanced DetailsTbl with 'new column's using lookupvalue

The data in DetailsTbl look like

Date	Name	ResultInLCY	Currency	HeaderKey
Monday, 31 January 2022	A	25	EUR	H1
Monday, 28 February 2022	A	53	EUR	H1
Monday, 28 February 2022	B	92	GBP	H2
Monday, 28 February 2022	D	32	GBP	H_FebOnly
Monday, 28 February 2022	E	97	USD	H_FebMar
Thursday, 31 March 2022	A	74	EUR	H1
Thursday, 31 March 2022	B	12	GBP	H2
Thursday, 31 March 2022	C	45	AUD	H3
Thursday, 31 March 2022	D	12	GBP	H_FebOnly
Thursday, 31 March 2022	E	87	USD	H_FebMar
Saturday, 30 April 2022	A	53	EUR	H1
Saturday, 30 April 2022	B	74	GBP	H2
Saturday, 30 April 2022	C	98	AUD	H3

The data in FX table is like

Date	Currency	Rate
Monday, 31 January 2022	CAD	1.2
Monday, 31 January 2022	EUR	1.1
Monday, 31 January 2022	GBP	1.07
Monday, 31 January 2022	AUD	1.4
Monday, 31 January 2022	USD	0.9
Monday, 28 February 2022	CAD	1.3
Monday, 28 February 2022	EUR	1.034
Monday, 28 February 2022	GBP	1.6
Monday, 28 February 2022	AUD	1.3
Monday, 28 February 2022	USD	1
Thursday, 31 March 2022	CAD	1.35
Thursday, 31 March 2022	EUR	1.04
Thursday, 31 March 2022	GBP	1.64
Thursday, 31 March 2022	AUD	1.25
Thursday, 31 March 2022	USD	1
Saturday, 30 April 2022	CAD	1.23
Saturday, 30 April 2022	EUR	1.076
Saturday, 30 April 2022	GBP	1.64
Saturday, 30 April 2022	AUD	1.33
Saturday, 30 April 2022	USD	1

I wish to write 

ResultsUSD = SUMX(ResultsLCYTbl, ResultsLCYTbl[ResultInLCY] * TREATAS(FxTbl,FxTbl[Rate]))

However, I am unable to join ResultsInLCY with FxTbl. The matching i wish to do are on Date + Currency.

Thank you for your help.

[v-jialluo-msft · Accepted solution]

Hi  @alsm ,

Please follow these steps:
(1) Create a new column

ResultsUSD2 = 
VAR _R = CALCULATE(VALUES(FXTbl[Rate]),FILTER(ALL(FXTbl),FXTbl[Date] = EARLIER(DetailsTbl[Date]) && FXTbl[Currency] = EARLIER(DetailsTbl[Currency])))
RETURN DetailsTbl[ResultInLCY] * _R

(2)Final output

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