Filtering and “dynamic” dates and filters in business central are a very powerful tool.
However so many people who have used business central for years have no idea how to use these.
A boardroom Mum is bringing you a series of articles to level up your filtering skills in business central that will not only having you using the software like a pro but will make your life easier.
Let’s begin with date filters.
The majority of people are aware that .. means to in business central for example;
01/04/2024..30/06/24 gives that quarters date range.
Or ..30/04/2024 would give year start to end of April 2024.
I will link below the article explaining the differences in the chart of accounts balance fields and how to get the balances you want.
However you could use a date filter for the quarter by using Q.
Or for the current month = CM
Or even how to use dynamic date filters for last month end = -CM-1D
Now you might be thinking this makes now sense so let’s look at what these codes mean in business central;
C = Current
T = Today
D = Date
W = Week
M= Month
Q = Quarter
Y = Year
Using these you can combine a formula such as the previous month end, lets break that down.
- For previous
C for current
M for month
-1D to take away one day.
The best way to learn these is to play around with them in business central, head to the chart of accounts click add a date filter.
When you find one that works click save view and enter a name for this view.
This is useful as then each month end/new period that dates automatically update as it is using a dynamic or formula based filter.
Rename here
How to use dynamic date filters in Business Central to calculate Year To Date = -CM..T
This is using CM and T for current month and Today.
Further Date formulas that are useful;
P = Current Period
P-1 = Last Period
P+1= Next Period
Y= Current Year
Y-1 = Last Year
Y+1 = Next Year
Lets walk through some further examples for you to play around with;
10 days from today = 10D
10th day of the current month = D10
4 weeks from today = 4W
I would play around with different formulas in business central, they become easier the more they use them.
At first business central can seem over complicated and its easier to just run monthly reports as you can in other systems by just picking a month.
However the flexibility business central gives you in business central is truly powerful.
You can filter data as you can in excel.
A data analyst’s dream!
You can also export it to excel if you want!
Look out for out next filters article that will cover wildcards.
If you are unsure how to write a specific formula for a date in business central drop a comment below and I will assist.
