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.
Hello Frank,
This docs site specifies the formulas used for foreign currency translations.
docs.microsoft.com/.../financial-reporting-currency-capability
Best regards,
Ludwig
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.
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:
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 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.
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
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
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
CA Neeraj Kumar 753
André Arnaud de Cal... 752 Super User 2025 Season 2
Sohaib Cheema 534 User Group Leader
