Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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
)
It worked! Many thanks!
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
)
User | Count |
---|---|
86 | |
82 | |
68 | |
66 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |