05.06 Working with Dates

05.06 Working with Dates

Overview

Almost every transactional report is about a period of time: invoices raised last month, orders due to ship in the next fortnight, stock movements year to date.

You can, of course, filter on a fixed range of dates, for example 2026-01-01:2026-12-31 for the whole of 2026, as described in 02.02 Layout - Filters. But a fixed date range has an obvious drawback: next month, somebody has to remember to change it.

Dynamic date filters solve this. Rather than specifying the dates themselves, you pick a date variable, such as "Last Month", and FastClose works out the actual dates each time the report is run. This makes them ideal for reports that provide a changing, rolling view of the data, day by day or month by month, and especially for reports that are scheduled.

FastClose ships with a good selection of date variables out of the box. And when the one you need isn't there, you can create your own using Calculated Dates.

Here is a video, or read below - don't forget the exercises at the bottom!

[Video placeholder]- New module so not yet filmed

Choosing the Right Date

First, decide which date you want to filter on. Transactional data often carries several. For example, an AR Invoice line may have an Invoice Date, a Due Date, a Ship Date and an Applied Date, and each answers a different question: "invoices raised last month" and "invoices falling due in the next two weeks" use different date fields.

Make sure that date is in your filter area, adding it via Layout > Add / Remove Filters or Field Explorer if necessary.

Using the Built-in Date Variables

Clicking on the magnifying glass in a date filter will open the filter selector, where the dynamic variable drop down can then be expanded:

image

Here you will find the most commonly needed dates and date ranges, including:

If you then select one the filter will show the variable rather than fixed dates, so that it's clear what is going on.

Creating Your Own: Calculated Dates

The built-in variables cover a lot of ground, but real reporting needs are often more specific: the next two weeks, the last 30 days, the last twelve complete months, from the start of the week to today. For these, you need to be able to create your own Calculated Dates and Calculated Date Ranges,

There are two kinds of calculated date:

Ranges are built from members. So when you create a range, you usually create the dates at each end of it first, unless suitable ones already exist - and then the range.

Step 1 - Open the Variables Dialog

In a report such as AR 02 Invoices, go to Insert->Variables and select Add a Calculated Date from the drop down on the left.

image

Step 2 - Create the Members

Click Add Member, give it a name, and enter an expression that returns a date. The expression builder works in exactly the same way, and uses exactly the same syntax, as Calculated Fields, which we covered in 05.05 Calculated Fields.

For example, to create a date 28 days from today:

AddDays(Today(), 28)

Or to return the first day of the current month:

Date(Year(Today()), Month(Today()), 1)

NB: The first day of the month is actually a built in item already, this is just for the example.

Step 3 - Create the Range

Click Add Range, give it a name, and choose the members for the start and end of the range.

So a Next Four Weeks range would run from Today to the Today Plus 28 member created above.

image

Step 4 - Use It

Once defined, your new range appears as an option in the date filter selectors, alongside the built-in variables, ready to be applied to any date field in the report.

Built in Variables

The following variables for use in date selectors or ranges, are pre-defined:

Yesterday

StartOfThisMonth

EndOfThisMonth

StartOfThisWeek-Mo-Su

EndOfThisWeek-Mo-Su

Today

StartOfLastMonth

EndOfLastMonth

StartOfLastWeek-Mo-Su

EndOfLastWeek-Mo-Su

Tomorrow

StartOfNextMonth

EndOfNextMonth

StartOfNextWeek-Mo-Su

EndOfNextWeek-Mo-Su

StartOfThisQuarter

EndOfThisQuarter

StartOfLastQuarter

EndOfLastQuarter

StartOfThisWeek-Su-Mo

EndOfThisWeek-Su-Mo

StartOfNextQuarter

EndOfNextQuarter

StartOfLastWeek-Su-Mo

EndOfLastWeek-Su-Mo

StartOfThisHalf

EndOfThisHalf

StartOfNextWeek-Su-Mo

EndOfNextWeek-Su-Mo

StartOfLastHalf

EndOfLastHalf

StartOfNextHalf

EndOfNextHalf

StartOfThisYear

EndOfThisYear

StartOfNextYear

EndOfNextYear

StartOfLastYear

EndOfLastYear

Some Useful Expressions

Date

Example expression

Today plus / minus n days

AddDays(TODAY(), 14) or AddDays(TODAY(), -30)

Previous working day

AddDays(Today(), IF(WEEKDAY(Today(), 2) = 1, -3, IF(WEEKDAY(Today(), 2) = 7, -2, -1)))

Five working days ahead

AddDays(Today(), 7)

First day of the month, 12 months ago

EDATE(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), -12)

Monday of this week

AddDays(TODAY(), -WEEKDAY(TODAY(), 2) + 1)

And some examples of ranges you could build from them:

Range

From

To

Next Two Weeks

Today

Today + 14

Last 30 Days

Today - 30

Today

Last 12 Complete Months

First day of the month, 12 months ago

EndOfLastMonth

Week to Date

Monday of this week

Today

Tip: You don't need to be an expert in date formulae. Ask the AI Help assistant on the main Designer screen. Describe the date you want, for example "the last day of the previous month", and it can provide the expression for you.

Putting It to Work: Scheduled Reports

Dynamic date filters really come into their own when combined with the Scheduler. For example:

Tip: When designing a report for scheduling, think carefully about when it will run. A report filtered on "Last Month" makes perfect sense on the 1st, but if it is scheduled for the 15th, it will still report on the previous calendar month. If you want a rolling window, create a calculated range such as "Last 30 Days" instead.

Dynamic Filters and Date Summaries

Dynamic date filters are also the natural partner for the date summarising techniques in the next tutorial, 05.07 Summarising Transactions into Time Periods. For example, a report filtered to the Last 12 Complete Months and then summarised by month gives a rolling twelve month view that always stays up to date.

Exercises

Now try the downloadable exercises below:

05.06 Dynamic Date Filters - Exercises.pdf

Your feedback helps us do better, to fill in a short survey on this tutorial, click [here - survey link placeholder].

Powered By