I am trying to calculate prior year sales revenue in USD rate. I have column REVENUE_LC which is revenue in local currency, and I have column FX_RATE_USD. When I select year and month as 2017 and 12, the below table shows. Now I need to calculate prior sales revenue in USD, but it should account FX_Rate_USD for selected year and month which is 2017 and 12, can you help please?
The resulting sales revenue is highlighted in yellow. its from
DIVIDE(PRIOR YEAR SALES REVENUE_LC,FX_RATE_USD)
I did create calculated column, the below column, gives me the fx rate for 2017 and month 12, however as soon as I bring the previous year sales, the rate shows for selected year as well as previous year.
VAR selectedmonth = RELATED('DIM DATE'[Month])
VAR selectedyear = RELATED('DIM DATE'[Year])
VAR selectedday =01
VAR DATEdt_KEY = CONCATENATE(CONCATENATE(selectedyear,FORMAT(selectedmonth,"00")),FORMAT(selectedday,"00"))
VAR lookuprate =LOOKUPVALUE(Rates[Current FX rate USD],Rates[FISCAL_DATEKEY],VALUE(DATEdt_KEY)