List of monthly/Balance sheet or Income statement exchange rates/different currencies - how to calculate in Powerpivot

[abalserv]

hi Folks
I'm a Powerpivot learner - just starting to get to grips with the power of this. My question is this..
I have a table of transactions. There is one field called Accounted_currency which is in the local currency e.g. yen for China. They all have to be converted back to euros.
These transactions can be balance sheet or income statement transactions. The exchange rate for each of these (which is in a preset table: fields - month/year,currency name, Balance sheet or Income statement, currency rate) will vary depending on the month/year, currency used and whether it's a Balance sheet or income statement. How can I set up a powerpivot that will show me the calculations for each of these currencies e.g. if it's a BS items for Oct-13 for yen, that's .5 (original transaction = 1000, amount to show up is 500). I've set up a matching concatenated field to link them but I'm not sure whether I should be looking at SumX or Calculate - even some pointers would be really helpful. Thanks.

 

[Tycho01]

Hey,
It sounds to me like you'd wanna get a lookup table with columns for the info like currency/month/rates + BS/IS as you mentioned, then base your lookups on that. Basically you'll want for your other table to have information available on which currency/month/whatever to use as well; at that point you could make the lookup query like =LOOKUPVALUE(lookuptable[ExchangeRate],lookuptable[month],[month],lookuptable[currency],[currency],lookuptable[BSIS],[BSIS]). That way you'd look up the right value from the lookup table filtering it down to the right row based on the circumstances.

 

[abalserv]

Hey,
It sounds to me like you'd wanna get a lookup table with columns for the info like currency/month/rates + BS/IS as you mentioned, then base your lookups on that. Basically you'll want for your other table to have information available on which currency/month/whatever to use as well; at that point you could make the lookup query like =LOOKUPVALUE(lookuptable[ExchangeRate],lookuptable[month],[month],lookuptable[currency],[currency],lookuptable[BSIS],[BSIS]). That way you'd look up the right value from the lookup table filtering it down to the right row based on the circumstances.

OK, am going to try that out. I have a look up table set up with all the details you mention but I was playing around with the related function rather than the lookup one you have here. Thank you very much.

 

[Tycho01]

Yeah, it's only a week or so ago I figured it out myself. Related is the easiest way for predefined relationships, but in case of multiple 'keys' you need something more explicit like this instead.

 

[abalserv]

yes, went back to drawing board on this one. Rejigged the list of exchange rates. Created matching concatenated fields and did a relate formula that did the job :- pulled in the correct exchange rates depending on year/month, currency,balance sheet or P&L ). Next step now is to figure out how to correctly calculate the balance sheet cumulatives and the P&L calculations thanks A