Using Advanced Date ranges in filters of D365FO (or AX)

In a previous article I have explained the extensive filter options you can use via the ‘Advanced filter or sort’ option. In this filter you can also use date ranges to use date selections that moves along with the current date. This can be used to create one-off filters for time periods, but also as a starting point for tiles that you can use on workspaces.

What are these date ranges like DayRange or GreaterThanDate? How do they work and how can you use them in D365FO (or AX)? And what is the effect of the session date when using these filters? That's where this article is about.

The concept of date ranges

Let's say you have a list of sales orders and wants to use the information based on the (added) column 'Created date and time':

For example you want an overview of sales orders created in the last 7 days. This is where I can use a date range filter for. To do this, on this List page I open the ‘Advanced filter or sort’. Here I have selected the ‘Created date and time’ field and use one of the date ranges options, namely the 'DayRange' option:

In this situation I use the following filter criteria:

(DayRange(-7,0))

I will explain this formula based on the parts related to it:

(DayRange*(-7**,0***))

• *DayRange:? this means that I will calculate an amount of days based on today. This is specified by using the word ‘DayRange’ in the formula. Alternatives are? ‘MonthRange’ and ‘YearRange’.
• **-7: this is the position that clarifies how long you will look back from today. In my situation this is? -7. Based on the fact that I use the ‘Dayrange’ option, it means that I will look back until 7 days.
• ***0: this is the position that clarifies how long you will look forward from today. In my situation this is 0, which means that I will only look back and not look to future dates.

Summarized: In this situation the filter value (DayRange(-7,0)) will show me the sales orders only created in the last 7 days:

When I save the outcome in my view I can add this list to a Workspace, so that I always have the actual picture of how many sales orders are created in the last seven days:

There are other variants to use for these date ranges. Therefore a next example, the filter to find all date and time entries after the current time:

(GreaterThanUtcNow ())

Let's say I have a list of batch jobs with scheduled start date/times in the past and in the future:

Now I can use the (GreaterThanUtcNow ()) date range option:

The outcome is that only batch jobs with a scheduled start date/time after the current time will be presented based on this filter:

Besides these day range and current time filter, you can also use a variance like month range:

(MonthRange(0,3))

Again the explanation about how this filter works:

(MonthRange*(0**,3***)).

• *MonthRange:?this means that I will calculate an amount of months based on today. This is specified by using the word ‘MonthRange’ in the formula. Alternatives are?‘DayRange’ and ‘YearRange’.
• **0: this is the position that clarifies how long you will look back from today in months. In my situation this is?0. Based on the fact that I use the ‘MonthRange’ option, it means that I won't look back in months.
• ***3: this is the position that clarifies how long you will look forward from today calculated in months. In my situation this is 3, which means that I will look forward to the next three months.

For example I use this filter in the Procurement and Sourcing inquiry for Open purchase order lines for the field 'Requested receipt date':

The result is that based on today only the requested receipt dates for the current and next 2 months (in total 3 months) will be presented:

The date range in relation to session date

When changing the system date, the date range will work related to this selected session date. For example I change my session date to 1-1-2017. This because in the Microsoft Contoso demo environment a lot of transactions have a date in 2017:

Now I open the Open purchase order lines inquiry again with the same filter as the example above. Now the outcome is related to the first three months of 2017:

The easy D or T

Do you want a filter with only the outcome related to today? Then in the date field easily select the 'T' or the 'D':

This will give the transactions of the current day, which means that you don't have to change the filter if tomorrow you want only the transactions of tomorrow. The difference between the 'T' and the 'D':

• T = System date: date when using the pc (no relation to session date in D365FO)
• D = Session date: selected session date in D365FO

Do you want to see all the filter syntaxes and date range options? Check the following Microsoft Learn link: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/fin-ops/get-started/advanced-filtering-query-options