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.
RATEFX = 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) ,Rates[FXRATES],'Sales'[CURRENCY_REPORTED]) RETURN lookuprate
The table is the result I am trying to get
|Customer||Rate FX||REVENUE_LC||REVENUE_USD||PRIOR YEAR SALES REVENUE_LC||PRIOR YEAR SALES REVENUE_USD|
I am attaching a mock up file
Hi @EV ,
I'd like some sample data to help us clarify your data structure and do test to coding formula:
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
Proud to be a Datanaut!