PREVIOUS YEAR REVENUE to current year rate

DAX DAX Calculations
1

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

image
image950×431 19.5 KB

I am attaching a mock up file [TEST file]
(https://1drv.ms/u/s!AoC3T84cA7LGjlpeTWlcoHPycX_j)

image
image624×550 107 KB

image

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.

2

I’m unable to download the file. You can add it directly into the forum post here

image

On first look this is not that difficult, but you have made it so by having a model that is very sub optimal.

This is not how I would recommend setting this out at all.

image

Certainly review this course module when you can as it goes through all my best practices for this area of Power BI. The model is so important when working with things like this.

If you want to learn how work with FX rate effectively, check out this video here.

This can likely be solve quite easily with the correct model, overlaid with the right DAX formula.

All you will need is the Sales table, Rates table (both fact tables), then have the Date table (lookup table) above these. You don’t need the previous dates table at all. Time intelligence functions can complete all this hard work.

Also you will not need to do anything with a calculated column.

I would like to see more of the entire setup and be able to download the model before advising any further.

Sam

3

Thank you very much for your response. Sorry I didn’t remove those Previous date table, I did not use them in the logic. I did go through other forum posts related to this topic and started from the video attached. It did help me a lot to get into building the DAX calculations, thank you so much for that, however I couldn’t resolve it, it required lot of summarization, as well as kept returning incorrect total.

I was able to come up with a solution and seems to be working as expected. Once it is completely tested, I will post the test file and mark this as complete. Here is the approach I used.

  1. Add same Rates table to the data model
  2. Created a date key for next year. example, for 20180101, it returns 20190101 in Sales table
  3. Join the Second Rates to Sales table using the step 2 key.
  4. Convert the Revenue in Local currency to USD using the rates from second table

DIVIDE('Sales'[REVENUE_IN_LOCAL_CURRENCY],RELATED('Rates 2'[RATE_USD]))

  1. Use this converted Revenue for all the Previous year calculation.
4

Ok great, I’m honestly sure there a simpler solution but would need to see a demo file I think to assist here.

5

I was able to solve this. Thank you