dEPM Consolidation - 12 months calculation method for Exchange Average Rate

joao-miguel

Hello to all;

I need have the average rate calculated in a 12 months base, instead the YTD method applied in dEPM.

Anyone faced this requirement? Is possibly change the calculation method without resorting to customization?

Is possible disable the calculation and deal the average rate as a fixed rate ?

I will appreciate your opinions / suggestions.

Thanks for your time

João Miguel

Red Gold use of Role Security in LX.pptx

Find more posts tagged with

Infor EPM Discussions

Comments

Adolf Staudigl

Hi João!

I would need more information about your requirement.
Please attach an example in Excel, if possible.

The application doesn't care what exchange rate you enter for rate type A. It is simply an exchange rate that is then used for the conversion.
If you only want to use the exchange rate for the current period (month to date), the weighted average method (WA) may be the right concept for you. See https://docs.infor.com/depm/2025.x/en-us/depmolh_cloud/default.html?helpcontent=financial_consolidation_cloud/rrz1600430454583.html

Nathan Hewat

@Adi- I believe João might be complaining about what the system does to the values (ExchangeRateType = "PA") as it transfers them from the Integration table to the TRATES cube during the standard import process...

By design, it defines "Average Rate" thus:

Average Rate = ((number of days of period 1) * ('PA' exchange rate for period 1) + (number of days of period 2) * ('PA' exchange rate for period 2) + ... + (number of days of target period) * ('PA' exchange rate for target period)) / ((number of days of period 1) + (number of days of period 2) + ... + (number of days of target period)); e.g. Average Rate = ((29 * 1.1) + (31 * 1.2) + (30 * 1.3)) / (29 + 31 + 30)

@João - the current best workaround I've found so far is to leave the ExchangeRates Integration table empty and load the correct values directly to the TRATES cube from a table/view in the Staging database via an App Engine script.

reginald-mompoint

Hello,

The integrations will not allow you to do this currently. There's already an enhancement request for this and I personally have submitted tickets for this as well. The workaround for now is to use the Standard Exchange Rate Maintenance screen in Consolidations to enter your rates. Then, the Method on your Accounts (Group Account Business Object in Business Modeling) should be set to the "A" as opposed to "WA". Tested this with a client and worked for me.

Hope this helps.

Nathan Hewat

@Reginald - which is the ER you created?

Here are some other ERs relating to the loading of FX rates (including João's):

https://mingle-portal.us2.prd3.inforcloudsuite.com/v2/CONCIERGE_PRD/?LogicalId=lid://infor.cxp.1&Tab=ERS&ERId=107646

https://mingle-portal.us2.prd3.inforcloudsuite.com/v2/CONCIERGE_PRD/?LogicalId=lid://infor.cxp.1&Tab=ERS&ERId=109101

https://mingle-portal.us2.prd3.inforcloudsuite.com/v2/CONCIERGE_PRD/?LogicalId=lid://infor.cxp.1&Tab=ERS&ERId=114976

@João - you should probably add a worked example to illustrate exactly what you mean, as it's not obvious from the 2 sentence summary on the ER.

Anyone who cares about this functionality should go ahead and vote on the ERs. This helps the Product Manager prioritise what new / enhanced functionality gets onto the Roadmap and in what order.

reginald-mompoint

Hello,

I didn't submit an ER due to there was a KB article on this and I had submitted a ticket/incident case to Infor Support in the past on my client's behalf.

The 2nd link that you provided, I endorsed that ER in December 2024. However, it is referring to a different topic with loading the rates using a flat file directly in the Standard Exchange Rate Maintenance screen. I see this as similar to how you can get a template and upload journals. This functionality will be a good option as well for clients if they want to load a flat file into Consolidations if the rates are not coming from the Integration.

I endorsed Joao's ER as suggested.

Thank you

joao-miguel

Hi Adi;

First let me thanks for your interest and sorry for take so long to reply.

I have a excel file with a simple example of what I pretend, but I do not find way how to attach here.

If you need more details, you can reach me via Teams (joao.miguel@nls-group.eu)

I try a workaround that was only upload rate type F, and via application, manually record rate type A. However when you run the routines to upload data from relational modeling, the rates you input manually are cleared !!!. Does not make sense.

Thanks for your time

J. Miguel

Nathan Hewat

Hi João Miguel

Yes, that mixed approach won't work... I suggest you try either one of the following:

Create a report using Excel Integration that writes the required data directly to the TRATES cube using the INFOR.WRITE.VALUE() function. I'd do it via the following steps:

Use Self-Service to create a basic report showing the view of the cube you want to be operating on.
Use the "Copy Report Defnition..." feature to transfer it to Excel Integration.
Play around with the formulae to write appropiate values to the cube
Use native excel functionality to polish it up to an acceptable standard (e.g. formatting for look&feel, condtitional formatting to highlight issues, check features etc).

Create an App Engine process to write data that you've collated using a Relational Modeling view/table directly to the TRATES cube. This is slightly more involved - myself or other colleagues from Infor GPS can assist if wanted.

Best

Nathan

Copyright © 2025 Infor. All rights reserved.