How to select a conversion rate based on a date?

[AliceW]

Hi everyone,

I hope one of you knowledgeable souls might have a solution for me.

I have two tables, Opps and ConversionRates. The Opps have the Amount column in a local currency, while ConversionRates has several rates for each Currency. I'd like to pick one of these currencies (they all convert to EUR) and bring it in the Opps table. It depends on the Opp Date column, as it needs to be between the rate start and end date

Opps:

Opp Number	Date	Currency	Amount	Rate
111	01/01/2020	USD	100	1.7
222	30/06/2020	BRL	100	4

ConversionRates:

Currency	Rate	Start	End
USD	1.5	01/01/2018	01/01/2019
USD	1.7	02/01/2019	01/01/2023
BRL	4	01/01/2017	31/12/2020
BRL	5	01/01/2021	31/12/2022

It's going to be a many to many relationship, so just a RELATED won't work.. In the example above, the USD rate is 1.7 as the Opps.Date falls between the ConversionRates.Start and End in the second row.

Big thank you,

Alice

[v-stephen-msft · Accepted solution]

Hi @AliceW ,

Try this measure

Rate = 
CALCULATE (
    SUM ( 'ConversionRates'[Rate] ),
    FILTER (
        'ConversionRates',
        [Start] < MAX ( 'Opps'[Date] )
            && [End] > MAX ( 'Opps'[Date] )
            && [Currency] = MAX ( 'Opps'[Currency] )
    )
)

Tips: There's no relationship between two tables.

You can check more details from here.

Best Regards,

Stephen Tao

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

[Anonymous]

Either you solve this in DAX where you lookup the conversion rate in the unrelated table ConversionRates 

Or you create a table where each date / currency comnination holds a value and the key is a concat of currency and date

eg

column 1                   column 2

USD 01/01/2018       1.5

USD 02/01/2018       1.5

..

USD 01/01/2019      1.5

USD 02/01/2019      1.7

Simular you create a key in the opps table and you can create a relationship between the keys

[AliceW]

Would you have a formula to help me look up the rate in DAX, please?