OK! This one is fun. It's not incredibly complicated, so hopefully someone can nail it out pretty quick.
The basics: I'm trying to recreate an income statement based on a list of transactions from our database. The only catch is that the transactions are in multiple currencies, and we'll be working with multiple periods. We have another table with the relevant exchange rates by period. Alright, I lied, there's one more catch: the tables do not share any unique dimensions to link the transactions to the correct exchange rate in the rate table (so I'm having to calculate one) and they are in separate databases (if that matters).
I've attached a sample file with [hopefully] enough data extracted to get the job done. As things stand, the unique calculated field is a combination of "Period" (which is in the YYYYMM format) and the currency code of the transaction; an example of this would be 201303GBP, for a Period 3 transaction in pounds. To make this even more fun, if we consider the "from" currency and to "to" currency in the conversion process, our rate table also includes many "to" currencies, other than USD. So, in order to make the calculated column function properly, I believe we also have to somehow filter out all of the non-USD "to" currencies in the rate table.
I know that this process is probably easier to manage by aggragating the data before using Tableau, but ideally we'd prefer to keep everything fully automated. As an aside, we were already able to fully automate this using PowerPivot and the related() function - let the games begin!
UPDATE (w/ workbook attachment notes): If you're one of the kind folks who is trying to work with the attached workbook, it's probably helpful to mention a few things:
1) The income statement begins at account 401.00 and includes all accounts after it (net income = the sum of 401.00 : 999.00)
2) You should be able to validate the total by using the SUM([amount]) without any calculations, because all of the foreign currency transactions for this particular group of data should cancel one another out.
3) Period 3 is actually June. Our fiscal year begins in April.
Thanks in advance for you help!