Forum Discussion
How to select a conversion rate based on a date?
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
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.
3 Replies
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.
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
Would you have a formula to help me look up the rate in DAX, please?
