Customising a report's date filtering

Use the report filter formula to apply advanced date filter logic

Sometimes the built-in date filtering doesn't quite do enough. Some examples where custom date filtering would help are:

• Switching between "Created Date" for new jobs, and "Completed Date" for finished jobs
• Including a "previous period"

Setting up a formula based date range filter

The first step is enable the date range filter, and pick Row Filter Formula:

Next, you'll need to add some logic to the formula. The user-selected dates will be available as [Date Range Start] and [Date Range End].

Here is an example which does a simple check on Invoice Date:

Here is another example formula which applies logic based on status: 

IF([Status] = "Completed",
   [Completed Date] >= [Date Range Start] AND [Completed Date] <= [Date Range End],
   [Opened Date] >= [Date Range Start] AND [Opened Date] <= [Date Range End]
)

Here is another example which includes a previous period:

([Invoice Date] >= [Date Range Start] AND [Invoice Date] <= [Date Range End])
OR
([Invoice Date] >= ([Date Range Start] - INTERVALVALUE("1 year")) 
 AND 
 [Invoice Date] <= ([Date Range End] - INVERVALVALUE("1 year"))
)