Constant currency analysis is very popular when we need to take out FX rate fluctuations from our analysis. There are many different methodologies that companies used for determining what constant FX rate to apply to the historical values. In our tutorial, we will take a look at the time window for our analysis, figure out what the latest exchange rate was for each currency and then restate the entire history using this rate. In other words, if the exchange rate was always the same as what it was on the last day of our time window, what would the trend look like?
You can download the Power BI model used in this tutorial here.
I have created a couple of measures to make testing a bit simpler for you. The process is as follows:
- Create a measure Sales LC to calculate sales in local currency
- Create a measure Constant Currency Date to allow us to figure out what was the latest date in our FX Rates table that actually had an FX Rate in our time window
- use that date to look up the rate for each currency used in our Sales table as we create our Sales USD (CC) calculation (USD Sales using Constant Currency Rate)
Please compare our Constant currency rate measure to the Sales USD measure to see the difference in approaches.
DAX Formulas:
Constant Currency Date = CALCULATE(
MAX('FX Rate'[Date]),
ALLSELECTED('Date')
)
Constant Currency Rate =
var cc = [Constant Currency Date]
return
CALCULATE(
MAX('FX Rate'[Fx Rate to USD]),
FILTER(
ALL('FX Rate'),
'FX Rate'[Date] = cc
&& 'FX Rate'[Currency] = SELECTEDVALUE('Currency'[Currency])
)
)
Sales LC =
SUM(Sales[LC Amount])
Sales USD =
VAR vSalesWithFXRate =
ADDCOLUMNS (
SUMMARIZE (
Sales,
'Date'[Date],
'Currency'[Currency]
),
"_LC Amount", [Sales LC],
"_FX Rate to USD", CALCULATE (
SELECTEDVALUE ( 'FX Rate'[Fx Rate to USD])
)
)
RETURN
SUMX (
vSalesWithFXRate,
[_LC Amount] / [_FX Rate to USD]
)
Sales USD (CC) =
var ccDate = [Constant Currency Date]
VAR vSalesWithConstantRate =
SUMMARIZE(
Sales,
'Currency'[Currency],
"_LC Amount",
[Sales LC],
"_Constant Rate",
CALCULATE(
MAX('FX Rate'[Fx Rate to USD]),
FILTER(
ALL('FX Rate'),
'FX Rate'[Date] = ccDate
&& 'FX Rate'[Currency] = SELECTEDVALUE('Currency'[Currency])
)
)
)
RETURN SUMX(vSalesWithConstantRate, [_LC Amount]/[_Constant Rate])
business intelligist 
Very helpful and informative! Thank you for dedicating the time to put this together!
Thank you for the kind words, you are very welcome!