You are going to enjoy this challenge. I spend last 5 weeks trying to find a resolution. Believe me, I checked all possible solution. At the same time, I am convinced I am missing something really simple or may be complex.
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 RateFx When I select year and month as 2018 and 12, the below table shows. Now I need to calculate prior sales revenue in USD, but it should account RateFx 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,MIN(Sales[RateFx]))
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
I am attaching a mock up file [TEST file]
I hope I followed the rules forum questions, in case i missed something please forgive me. Please let me know if you need anything to help clarify my question. Thank you very much in advance.