Balance, Balance at Date, Net Change and Date Filter in Microsoft Dynamics 365 Business Central.

You think you are the only one that is confused?

I can assure you, you are not alone. I think this cause some confusion for both new and some more experienced Business Central users.

What is a Date Filter ?

So I think we should start with a short introduction to what a Date Filter really is. In a more technical term – Date Filter is a flow filter. And flow filters are used in Business Central on a specific field type that is often referred to as flow fields. Flow fields are found many places in Business Central. When you can do drill down on a field to look at details the field you do the drill down on will often be a flow field.

Examples of flow fields are the balance field on the Chart of accounts, the balance field on customers and vendors and “quantity on hand” field in the Item table.

Flow fields are often, but not always, calculated on the basis of flow filters. And the flow filters are typical the filters you find on pages or report request pages under the filter section called “Filter totals by:”. Here is an example from the Item List page and from the Chart of Accounts list page.

What happens when you use a flow filter like the Date Filter is that you put a filter on the underlying date that sums up to the total in our flow field.

What is a flow field?

A flow field can be of different types. But one of the most common flow fields are the one who sums up the result of underlying transactions. For example when we post something in the G/L Journal in Business Central – General ledger entries are created. These ledger entries sums up to be the balance for our general ledger accounts. So this means that the balance fields on the G/L Accounts are actually not updated as a fixed value on the G/L Account every time we do a posting. But the value of the balance field is calculated every time it is displayed, used in a report or by other processes in the system. And this is where some of the real technical people will correct me and say that the values actually are stored in sum index tables, but lets not go to deep into that for now.

So to repeat – every time we display a flow field, Business Central calculates the result of the underlying transnational data. This also means that what we see what do drill down on a flow field is the transactions that together makes the sum.

So lets look at this in the chart of accounts

As we can see here the balance on Account 60100 is 15 000.00 and when we do drill down on that field by clicking on the balance figure we get a view of the underlying transactions.

What happens to the different balance fields if I set a Date Filter?

If we again look at the Chart Of Accounts, it is a very common wish to be able to work with only the relevant accounts and often users want to hide the accounts without a balance or without Net Change. And at the same time they want to look at the net change or balance with in specific months or specific accounting periods. And in my experience this is where some of the Business Central users seems to start getting confused.

It is important to be aware of the fact that the balance field is not affected by the date filter you set when you work with the Chart of Account or General Ledger in general. So a typical mistake I see users do when they want to hide accounts without balance is to set a date filter and then set a filter for “Balance <> 0”. And if anyone is not familiar with what that <> filter does – it is the same as not equal to or different from. So in this case it will show all accounts where the amount in the balance field is different from 0.

So let us experiment a little with this on the Chart of Accounts. And the starting point will be the Chart of Accounts with no filter set.

So now let us add a filter for “Balance <> 0”.

So now it is time to get the Date Filter to play. If we look back at the drill down we did on account “60100 Rent Expense” we can see that the balance today is 15 000 and that we have transactions for both 2020 and 2021 posted on the account.

So let us now say that we want to focus on what happen in 2020. So we set a Date Filter covering the whole year from 01.01.2020 to 31.12.2020. And still only want to see accounts with a balance different from zero.

What we can see now is that the “Net Change” field have changed from 15 000 to 12 000 for the 60100 account. But the balance is still the same as in the previous screen shots and those showed a balance of 15 000. So what is going on here?

Well the Date Filter does affect the “Net Change” field but not the Balance Field. And why is that? And how should you the reconcile your General Ledger for 2020?. Then it is important to know that the balance field always shows the sum of all the transactions in the General Ledger independent of what date they where posted and what Date Filter you might have set. But the Net Change is affected by the Date Filter and is now only showing the Net Change for the period covered by the Date Filter.

So what do I do then to see the balance by the end of 2020? I cannot use the Net Change field for that because at least the Balance Sheet accounts needs to have a start balance when the year begins.

So for this there is an extra field in the system called “Balance by Date” that we should make sure is visible in our Chart of Accounts. So when we add that field to our Chart of Account page the page looks like this.

So now that we have the Balance at Date field visible things are beginning be more meaningful. Because now we can see that for the Income Statement Accounts the Net Change and Balance at Date match because they are both calculated with the value from the Date Filter. But the Balance column stays the same because that field is not influenced by the Date Filter.

Another important thing to notice now is that there is a difference in the way that the Date Filter is affecting the “Net Change” field compared to the the “Balance at Date” field. The “Net Change” field is calculated on the basis of the whole period covered by the Date Filter but when it comes to the “Balance at Date” the calculation is actually done by looking at all transactions for the account including the last date covered by the filter. So written in Business Central filter terms it will look like this. The effective filter for “Net Change” is “01.01.2020..31.12.2020.” but for the “Balance at Date” the filter is actually calculated as “..31.12.2020″. And this is done by design so that we as users don’t have to set Date Filter separate for the two fields”. Pretty smart if you ask me. But important to know and to understand.

We are also by now beginning to see the potential pitfall here for using the Balance <> 0 filter when we actually want to see the status in the period covered by our Date Filter. Because what would happen if an account have a “Balance at Date” on the 31.12.2020 but the Balance at our time of reporting is Zero and we use the filter “Balance <> 0”. You guessed correct. An account with that status will not be visible in our “Chart Of Accounts” when we look at status at the date of 31.12.2020.

We can demonstrate this. If I post a transaction for Account 60100 of -15 000 in 2021 – we can see the effect.

So after posting -15 000 to 60100, keeping the Date Filter set to “01.01.2020..31.12.2020” but removing the “Balance <> 0” filter. The “Net Change” change and “Balance at Date” is still 12 000 as expected and the Balance is now Zero.

So now lets add the “Balance <> 0” filter again.

Now we can see that even though we want to see the status for 2020 account 60100 is not showing in our Chart of Accounts. But we are still showing accounts with a zero “Balance at Date”. So of course building our reporting of the figures showing in this page now will be incorrect.

So the proper way to hide accounts with zero balance at your time of reporting when you use the Date Filter is to set the filter “Balance at Date <> 0” and not “Balance <> 0”. Again because balance shows the sum of all transactions in the system regardless of how the Date filter is set.

So lets change our filter again.

So now with “Balance at Date <> 0” and “Date Filter 01.01.2020..31.12.2020” account 60100 again is shown in our Chart of Accounts. And only accounts with a balance at 31.12.2020 is visible. This makes it easier for us to see what accounts we need to focus on for our reporting.

One last thing to remember is that this also applies to the request page for G/L Report. For example for the “Trail Balance reports”. When you use Date Filters and want to hide accounts with zero balance. But your filter on “Balance at Date” and not on the “Balance field.”

Happy reporting!