Assign ClosingRate FX Rates within a Cube View

[Andy1]

We currently have a cube view built out to compare specific GL account values from quarter to quarter. Some of these values needs to be translated using using FX rates. We have AverageRate and ClosingRate, but when assigning the Currency in the Cube view it seems to default to AverageRate, but the requirement is to be using ClosingRate values. 

Is this best way to do this through an XFBR rule? Help is greatly appreciated, thanks!

[MarkBird]

Hi Andy

What type of accounts are these? Typically you would expect the accounts to translate based on their account type:

and the cube properties:

What is the purpose of the comparison cube view? Are you expecting the accounts to translate at closing rate in the cube view, and at average rate during consolidation?

[Andy1]

Hi @MarkBird ,

Two of these accounts are liability and one is an asset account. 

The purpose of the cube view is just to view balances for these accounts for certain entities. The ask is just to have the cube view display the translation using period ClosingRates for review purposes checking values in the GL. 

Hope this information helps and thank you your help!

[MarkBird]

Have you configured flows for this accounts? I assume you are referring to the the EndBal or equivalent member, that looks something like this?

If so, make sure the Switch Type is set to false. Else it will treat those intersections a P&L (it will convert at average rate).

[KarlT]

Hi, I think if this is an issue with the underlying stored translated data then i would check the settings highlighted by MarkBird. However i notice you say "change the currency in the cube view" - is this being done by changing the Consolidation POV or do you have some kind of dynamic member in a different dimension that is enabling this view of different currencies? if the latter, it could be the logic in the dynamic calc that is causing it to always translate using AverageRate.

[Henning]

In addition to Karl's and Mark's answers, make sure to check on the scenario member if the Cube's FX settings are in use or not:

Can you also post a screenshot of your cube's FX settings? Just to make sure that those settings do not use average rate for all account types. E.g.

I had a case in the past where someone had the same problem and then realized someone entered average rates into both, AverageRate and ClosingRate members in the FX table.

If, as you say and Karl mentioned too, this is only relevant for a cube view and you actually translate all of your data using average rate, then you can use a dynamic member to display your data using a different rate, just for those three accounts in order to compare it to your GL as you say.