Prior year sales revenue converted to USD currency

[EV]

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
A	0.79	2140.587	2709.9468	320764	406081.7825
AB	0.88	1741.16	1980.8419	211.7902	240.9444824
AC	0.79	1092.316	1393.0825	5955.7022	7539.817952
AD	0.79	26668.37	33929.6695	18086.1814	22896.79884
ADD	0.79	1528.362	1949.1921	83246.4	105388.5302
AE	0.79	2585.442	3273.1251	31903.921	40389.82276
AG	0.79	-1042.99	-1330.1691	3321	4204.329662
AG	0.88	100425	114403.7237	2770.1108	3151.434357
AGRI	0.79	2093.899	2670.4493	33387.7765	42268.35865

I am attaching a mock up file

https://1drv.ms/u/s!AoC3T84cA7LGjlpeTWlcoHPycX_j

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[v-shex-msft]

Hi @EV ,

I'd like some sample data to help us clarify your data structure and do test to coding formula:

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[EV]

Test file

Here is my mock up file with selected tables. Im still struggling with this issue. Thank you very much in advance for looking into this.