I'm trying to confirm that this is how FR (MR) calculates a YTD, translated rollup of periodic results - I read an article that stated MR calculates the periodic balances month-by-month and then sums them for the YTD balance, but not sure if that's how it actually works. Does anyone here know if that's how YTD translated balances are derived? Thanks in advance.
And for those who are interested, it works as Erik said - I set this up in DEMF in Contoso, exported to Excel and ran some proof calculations
Hi Frank,
If you need your YTD column to be calculated same as you mentioned YTD month -1 * rate of month -1 + YTD month -2 * rate of month -2 .... because every month rate can be different
in that case you need to create separate columns for each month and create final calculated column for YTD and keep individual month column as non printing
This is another alternative if you want exact rate of each month to be considered for YTD
Thanks Erik! This is exactly what I was looking for. No need for a ticket - I just needed confirmation of my understanding/assumption as to how the YTD column worked.
Hello Frank,
A YTD column should translate at the current rate, if that account is set to "Current" (GL | Chart of Accounts | Main Account | Financial Reporting).
A YTD column will also include any balances from the prior year, if it is a Balance Sheet account.
Now if you you using AVG then a YTD column is going to take the AVG translated amount, from each of the prior periods and then translate them and sum them together.
Example:
| Period 1 | Period 2 | Period 3 | Period 4 | Period 4 YTD |
| 50.00 | 35.00 | 40.00 | 65.00 | $190.00 |
If you still have questions/issues it might be better to open a support ticket as we can then stream into your data and take a look.
Thank you,
Erik Johnson
Microsoft
Thanks Ludwig. I had read that article but it doesn't state explicitly how a YTD column is calculated. Say I'm 6 months through my fiscal year and I'm publishing a Profit & Loss statement that's been translated from GBP to USD. I believe the way the YTD column is calculated, MR takes Month1Results x Month1 rate + Month2Results x Month2 rate + Month3Results x Month 3 rate, etc. . . . to arrive at the 6 months to date total. The reason I'm trying to find something definitive on this is to convince a client that they don't need to configure a YTD average exchange rate every month because MR 'calculates' this for them. We may have to prove it to them via testing.
Hello Frank,
This docs site specifies the formulas used for foreign currency translations.
docs.microsoft.com/.../financial-reporting-currency-capability
Best regards,
Ludwig
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (