Find fx rate and convert amount to local currency from different table.

[TheBaz_57]

Hi all,

I seek your help once again.

I have two tables as follows:

#1

Name	Amount in USD	Local Curency	Date	Relationship column
Tom	5000	GBP	25/03/2024	GBP-25/03/2024
Peter	57	GBP	10/02/2024	GBP-10/02/2024
Sam	457	GBP	16/01/2024	GBP-16/01/2024
Tom	16	GBP	02/02/2024	GBP-28/02/2024
Peter	150	GBP	03/02/2024	GBP-03/02/2024
Sam	877	GBP	02/04/2024	GBP-02/04/2024

#2

Date	Currency	Xchrate	Relationship column
01/02/2024	AUD	0.58	AUD-01/02/2024
01/02/2024	GBP	1.02	GBP-01/02/2024
02/02/2024	AUD	0.69	AUD-02/02/2024
02/02/2024	GBP	1.05	GBP-02/02/2024
03/02/2024	AUD	0.63	AUD-03/02/2024
03/02/2024	GBP	1.002	GBP-03/02/2024
04/02/2024	AUD		AUD-04/02/2024
04/02/2024	GBP		GBP-04/02/2024
05/02/2024	AUD		AUD-05/02/2024
05/02/2024	GBP		GBP-05/02/2024

A relationship has been created based on the relationship columns. 

then in PBI I have a matrix like this

Name	Minimum Amount	Minimum amount date	Rate	Minimum amount in Local ccy
Tom	16	28/02/2024
Peter	57	10/02/2024
Sam	457	16/01/2024

What I need to find is the rate when the minimum amount occured and then get the amount in local ccy. 

For the rate I have tried the following but doesn't work:

Rate =

VAR _minQty = MIN('#1'[Amount in USD])

VAR _minDate = CALCULATE(

    MIN('#1'[Date]),'#1'[Amount in USD] = _minQty)

VAR _rate = CALCULATE(

    MIN('#2'[Xchrate]= _minDate)

RETURN _rate)

Thanks fo the help

[v-yohua-msft]

Hi, @TheBaz_57 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

[Greg_Deckler]

@TheBaz_57 If the two tables are related you should just have to do an aggregation on each column and multiply them together. Maybe I'm not understanding something but hard to test as the sample data doesn't include the related rows for the currency conversion.

[TheBaz_57]

 why my tables look so crappy 🙄