Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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...
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |