Forum Discussion
Rdarshana
Helper II
Helper IICurrency Conversion Data Model
Hi, I wanted to run the data model currency conversion for the group out here. This is what I have. The dimensions are all Dual Mode and Fact tables are Direct Query. Exchange Rates are ...
RdarshanaHelper II
Helper II Hi Anonymous ,
Thank you for helping with the DAX query.
However, that DAX query did not help me.
I am posting the query that worked for me, incase it helps someone.
Synopsis:
Financials Fact is joined to Calendar table, and data is stored at a daily level.
Exchange Rate table is not joined to any table in this data model. The rates are stored in a monthly level.
New Spend 9 =
VAR ConversionStartDate = MIN('Calendar'[Date]) -- Start date from slicer
VAR ConversionEndDate = MAX('Calendar'[Date]) -- End date from slicer
VAR LocalCurrencyCode = SELECTEDVALUE('Financials Fact'[Currency Code]) -- Local currency in Sales table
VAR TargetCurrencyCode = SELECTEDVALUE(exchange_rate[To Currency]) -- Target currency from slicer
-- Lookup the exchange rate within the selected date range
VAR ConvertedSales =
SUMX(
FILTER(
'Financials Fact',
'Financials Fact'[Date]>= ConversionStartDate && 'Financials Fact'[Date] <= ConversionEndDate
),
'Financials Fact'[Sale Amount] *
LOOKUPVALUE(
'exchange_rate'[Rate],
'exchange_rate'[From Currency], LocalCurrencyCode,
'exchange_rate'[To Currency], TargetCurrencyCode,
'exchange_rate'[Rate Date],EOMONTH('Financials Fact'[Date], -1) + 1 -- Rate Date is only at monthly level
)
)
RETURN
IF(ISBLANK(ConvertedSales), SUM('Financials Fact'[Sale Amount]), ConvertedSales)