Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

HCLTech Microsoft Business Applications Practice Blog

for Dynamics 365, Power Platform and the rest of the Microsoft technology stack


Filtering on Calculated Fields in D365 Business Central

Post Author: HCLTech |

There are many ways to access transaction history within Business Central. For instance, what if you are looking for cash transaction history for a month, a week, or even just one day? There are multiple reports you could run with date filters on the desired G/L account(s). However, if you want to see this data for a few different date ranges you’ll have to enter new dates and re-run the report each time. A quick and easy method to skip reports altogether while also allowing you to drill down to the data you’re looking for is to filter on a calculated field. Using the steps below, we’ll show you how to quickly get the data you need.

Using that cash account example, you’ll start with accessing your chart of accounts and selecting the G/L account for which you want to view history. Click on and enter the desired account. You’ll see two totals: the Net Change in account balance and the current account Balance.

If you click on Net Change now, you will see the entire transaction history for that account. However, we’re only looking for the transactions in a specific date range, so select the filter button instead.

This will bring up the filter pane. You’ll now see two filtering choices. The first is Filter list by:. The second option is the one you’re looking for – Filter totals by…

Select the second option and then click on the filter you would like to use. For this example, we’ll stick with date ranges and click on Date Filter.

Let’s say that you want to only see cash transactions for the 2019 calendar year. You can either enter the exact dates or use the formula “1/1/2019..T” (tip: T is a shortcut to signify “today” within a date field in Business Central. If you’re someone who tends to forget what date it is, this is a safe way to make sure that today’s transactions are included in the desired date range). You will see that your account balance is unchanged, but your Net Change has recalculated. You can click on this if you’d like to drill down and see the individual transactions.

You now have detailed information on only the transactions that add up to the Net Change for 2019.

If you want to change your view again and see the Net Change for only January of 2019? Enter those dates into the Date Filter and recalculate. Once again, you can click on Net Change and see a list that contains only those transactions.

Reporting tools are great but can sometimes be overkill or too slow when you’re looking for quick access to transactional data over multiple date ranges. In a pinch, filtering on a calculated field can be the tool you’re looking for.

Happy Dynamics 365'ing!

By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

PowerObjects Recommends