Exchange Rate Conversion with Date Range

[Anonymous]

Hey Everyone, 

I need to convert the value of revenue (total_revenue_usd in finance table) into other currencies (exchange_rate table). If the transaction took place '6/1/2022', it must be converted at the exchange rate of that day. The user can choose the type of currency (name in exchange_rate table), using the dropdown and in addition with the slicer the date range (baseline_date) when the sale took place - it can be just one day or several days.

Currently I've managed to do it in such a way that it works for a single day, but if I select a range of several days, it recalculates the value of the revenue based on the exchange rate of the last day selected in slicer. What I want is to recalculate each day separately at a given rate and then sum up the revenue value.

My data model without sensitive data:

I have the following metrics in my measure table:

Converted_Revenue = 
VAR _Date = [Currency_Date]
VAR _CurrencySelected = [Currency_Selected]
VAR _Revenue = Measure_Table[Revenue]
VAR _Rate = LOOKUPVALUE(exchange_rates_query[usd_exchange_rate], 
                        exchange_rates_query[name], _CurrencySelected, 
                        exchange_rates_query[day], _Date)
VAR _ConvertedMeasure = _Revenue / _Rate

RETURN
_ConvertedMeasure

where currency_date is max(dim_date(day)) and currency_selected = SELECTEDVALUE(Dim_Currency_Names[currency]).

Any help would be appreciated. Thanks!

[Anonymous]

It worked! Many thanks!

[johnt75 · Accepted solution]

Try

Converted Revenue =
SUMX (
    'Finance query',
    VAR currentDate = 'Finance query'[day]
    VAR chosenCurrency = [Currency Selected]
    VAR exchRate =
        LOOKUPVALUE (
            exchange_rates_query[usd_exchange_rate],
            exchange_rates_query[name], chosenCurrency,
            exchange_rates_query[day], currentDate
        )
    RETURN
        'Finance query'[total_revenue_usd] * exchRate
)