Dear DNA Team,
I am seeking your advice on how to handle multiple currencies.
The attached data contains payment dates in different currencies. The goal is to convert each sale to GBP or USD base on the selection.
There are two exchange rates conversion files one to GBP other to USD. Both are appended in the Power BI.
The below report shows the sales in different currencies:
The exchange currency rate is provided monthly not on a daily rate, in order to calculate exchange rate o daily level I used two different patterns that gave me exactly the same result:
- Monthly Exchange Rate =
- Exchange Rate =
VAR MonthYear = SELECTEDVALUE(‘Calendar’[Month&Year])
What I am trying to get is to convert each sale to USD and GBP:
If I select currency slicer then the given exchange rate is shown correctly:
Since the sales are in different currencies and I would like to display them in GBP and USD what would be the best approach?
I was trying to use the lookup value formula but then this didn’t work
I tried to use the pattern that Nick used in one of the miniseries:
Converted Sales =
VAR _Date = [Current Date] --Current Date in the Current Filter Context
VAR _CurrencySelected = [Currency Selected] --Currency Selected in the Current Filter Context
VAR _Sales = [Total Sales] --Measure we want to convert
VAR _Rate =
FactCurrencyRates[Crossrate] --Value to Bring back
,FactCurrencyRates[Ticker],_CurrencySelected --using the currency selected, lookup that value in the fact table
,FactCurrencyRates[Date],_Date --using the current date, lookup that value in the fact tabel
) /End Result is the currency rate of the currency selected on the current date/
VAR _ConvertedMeasure = _Sales * _Rate
Would you suggest creating a Variable Table (using addcolumns summarize)?
I will appreciate your advice as I would like to give it a go.