cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EV Regular Visitor
Regular Visitor

Prior year sales revenue converted to USD currency

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

CustomerRate FXREVENUE_LCREVENUE_USDPRIOR YEAR SALES REVENUE_LCPRIOR YEAR SALES REVENUE_USD
A0.792140.5872709.9468320764406081.7825
AB0.881741.161980.8419211.7902240.9444824
AC0.791092.3161393.08255955.70227539.817952
AD0.7926668.3733929.669518086.181422896.79884
ADD0.791528.3621949.192183246.4105388.5302
AE0.792585.4423273.125131903.92140389.82276
AG0.79-1042.99-1330.169133214204.329662
AG0.88100425114403.72372770.11083151.434357
AGRI0.792093.8992670.449333387.776542268.35865
 

I am attaching a mock up file

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

datamodel.PNGrates.PNG

3 REPLIES 3
Community Support Team
Community Support Team

Re: Prior year sales revenue converted to USD currency

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Super User
Super User

Re: Prior year sales revenue converted to USD currency

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...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


EV Regular Visitor
Regular Visitor

Re: Prior year sales revenue converted to USD currency

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 240 members 2,962 guests
Please welcome our newest community members: