Currency conversion based on type and date

[Anonymous]

Another day, another PBI case 🙂 I'm completely turned around with this one. What I'd like to do is determine a transaction value for a given conversion rate based on a given timeframe. I have two tables: 1) currency conversion rates to USD and 2) a log of transactions with i) a time and ii) a native currency value. See below:

currency_conversion table (i've exaggerated the exchange rates for this example to help determine if the logic is working given the difference in conversion rates are minimal and would be harder to spot):

transactions_table:

What I'd like to do is add to the transaction table a "usd_value" conversion column based on the "currency" and nearest match to "date_and_time" of the transaction in the transaction table (e.g., if it's 8PM it would take the latest conversion rate of 12PM that same day). Concetptually, the formula for doing so would end up being = transactions_table[value] * currency_conversion[conversion] . I'm struggling given the two conditions and what would seem like a many-to-many relationship between the tables. 

My questions are 1) do I need to create a calendar table to accomplish this and 2) what type of DAX can I leverage (have played around with LOOKUP value mentioned in other posts)?

Thank you very much for your time.

[johnt75 · Accepted solution]

You can add the below as a calculated column

USD Value =
VAR convRate =
    MAXX (
        TOPN (
            1,
            FILTER (
                'Currency Conversion',
                'Currency Conversion'[Currency Type] = 'Transactions Table'[Currency]
                    && 'Currency Conversion'[Date time] <= 'Transactions Table'[Date time]
            ),
            'Currency Conversion'[Date time]
        ),
        'Currency Conversion'[Conversion]
    )
RETURN
    'Transactions Table'[Value] * convRate

[Anonymous]

@johnt75 this is great. i need to look into TOPN, but this works.

out of curiosity and keeping in mind the size of the database, would you say that it's better practice to house the dates in the column headers and conversion populated horizontally in rows? I'm tempted to go this route, but the dates as column headers makes things a bit messy.

[johnt75]

I definitely wouldn't do that. A row per date per currency is the way to go.