Create a FX Table with Existing Data

[Anonymous]

I'll preface this inquiry by saying I'm a finance guy with some coding experience which makes me a little dangerous.  

The concept is essy, I can determine historical FX rates using exisitng data since our SAP records values as USD and LC for local currency.  USD vs. LC Euros for March 2016, USD vs. LC SGD for May 2015, etc.

I know I can create new tables from existing data, but is the new data static only?  Can i pull rows of months and currencies then use measures to get an aggregate of the USD value next to the aggregate LC value to then do FX calculations by currency and period or month?

I thought I might be able to do this within my existing data set, but I think it becomes far too complicated when you have many to one reporting.  If you have data with say five currencies rolled into one global report then I believe it is far more complicated to segregate those other currencies and get a consolidated FX neutral value.

That's my end game is to get FX neutral reporting.  Where if I have results today for February 2017 reporting then how can I get FX neutral reporting for either the same period last year or as compared to rates used in say September of the prior year when budget was set.

If anyone's eyes are lighting up because I noted same period as last year then I'll let you know we are on a fiscal 4-4-5 calendar and we use a custom dates table to not only set periods but also do YoY calculations.

Any suggestions are welcome and I'd really appreciate the insight.

[v-ljerr-msft]

Hi @Anonymous,

Have you tried the solution provided by ImkeF above? Does it work in your scenario? If you still have any question on this issue, feel free to ask.

Regards

[ImkeF]

I strongly recommend to turn the table with monthly FX-rates into a table with daily FX-rates in the query-editor. That way you can retrieve all necessary date-aspects from your date-dimension and don't have to bother about different date-granularities in DAX.

Simply:

1) Reference your date-dimension(table) and remove all columns, but: Date, Month and Year

2) Merge your FX-table on Month and Year with it and expand the necessary columns

3) Remove Month and Year- columns