Skip to main content

Hi Pigment Folks,

 

I have the following FX conversion rate challenge,

Context

I build my Balance Sheet from staging transaction amounts in local currency from Transactions list and structuring the amounts over a hierarchy were account number is the lower level.

My problem comes when trying to convert the amounts to our reporting currency.

 - I have a metric where I input the conversion rates on a monthly basis.

 - The balance sheet has the following behaviour: Every month, the cumulative amounts should be converting to the latest rates. And every year should be converting to the last month of the year rates.

Challenge

I’m looking to create an FX metric that I can apply to the staged amounts that it’s flexible.

I mean that whenever I’m showing data by years, quarters and months select the right rates.

For years would multiply the staged cumulative amounts by last month of the year rate
For month would multiply the staged cumulative amounts by each month rates
For quarters would multiply the staged cumulative amounts by the last month of the quarter

Hope you can help here :)

 

Thanks in advance.

Guillermo

Interested in the answer as well !!

Hi,

 

You can do that simply in Pigment

 

First you need your FX rates Metrics:

Typically you’ll have a metric “FX Rates” by Currency x Month x Version with your rates

You can Create a metric “FX Rates Quarter'” by Currency x Quarter x Version with the following formula

'FX Rate'

 

 

Then do the same for Year:

'FX Rate'pby last:Month.Year]

 

Now you can simply multiply you BS metrics by these.

Ask your question here!

Terms & ConditionsCookie settings