Rectifying incorrect YTD (Year to date) account balance issue in D365 Financial Reporter

Companies frequently do financial closing well after the commencement of a new financial year. So, if a company follows a January to December financial year, it is not uncommon to see the financial close happening in maybe February or March of the following (new) financial year. Till a given financial year is closed in Dynamics 365 the account balances do not carry forward to the new financial year. What this means is that the Year-To-Date (YTD) numbers reflect incorrect account balances from the previous fiscal year. By default, this is true for the financial reporter reports (e.g. Balance Sheet and Trial Balance) also.

One way to handle this in the financial reporter report is to add a column for the previous fiscal year and get the closing balance for the last fiscal year to add to the current years account balances using the CALC column, however this is a temporary fix and needs to be removed once the previous fiscal year is closed.

We will see a quick way to force Financial reporter to include balances from unclosed years on the YTD column. Let’s look at a very simple column building block that can be used on the balance sheet.

Notice, the currency display shows Accounting currency. This can also be left blank and it amounts to the same. Normally this is how a YTD is pulled up in a Balance sheet. If you run this the report financial reporter assumes a 0 current year opening balance for a given account hence the YTD balance is incorrect. We have highlighted on such account Raw Materials Inventory (#140100) for which a 0 YTD is being returned.

Let’s now make a small change to the column design and in the currency display row select a currency for which we don’t have a translation rate (this way when the system doesn’t find a translation rate it will assume 1 as the conversion factor).

In the column design shown above, we have selected a currency CHF. If you have access to sample data for USMF company you will see that there is no exchange rate defined for USD to CHF conversion.

When we run this Trial Balance report again using the same parameters, we get YTD balances that includes previous year account balances also.

Notice that we now have YTD numbers showing up for GL account 140100 – and for other accounts too.

The reason why the numbers have started showing up in the financial reporter output is because when there is a non-local currency in the currency display column the financial reporter does its own calculation for the Year to Date (YTD) numbers which considers the transactions from unclosed years also. This results in the correct YTD value showing up on the financial report. So with a very small tweak you can get your YTD numbers to start showing properly even for open years in the financial reporter output.

That’s it for now. We hope you have found our blog useful. Please stay tuned for more on Dynamics 365 by our team.