05.07 Summarising Transactions into Time Periods

05.07 Summarising Transactions into Time Periods

Overview

A list of transactions is useful, but often the real question is a summary over time:

To answer questions like these we need to do two things:

  1. Strip out the detail - remove most of the Down Axis columns (Invoice Number, Line, Part and so on), leaving only the thing you want to summarise by, such as Customer. As we saw in 02.05 Layout - Transactional Reports, removing detail columns automatically adds up and consolidates the values beneath them.

  2. Put each transaction into a time period based on one of its dates, and pivot those time periods across the report

FastClose offers two ways of doing the second step:

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

[Video placeholder] - New module so not yet filmed

Method 1: Calculated Fields

05.05 Calculated Fields introduced Calculated Fields as a way to create new dimensions derived from existing data. One of the most powerful uses is to transform a date into a time period label, such as a year and month, which can then be pivoted across.

Example: Sales by Customer by Month

Let's build a report showing invoiced sales by customer, by month.

1. Start with the detail. Open an AR Invoice Lines report and filter it to the time range you are interested in. A calculated date range such as Last 12 Complete Months on Invoice Date works nicely (see 05.06 Working with Dates - Dynamic Date Filters).

2. Create the calculated field. Switch to the Advanced tab, select Calculated Fields, add a new field named InvoiceMonth with caption Invoice Month, and enter an expression such as:

Text([Measures].[AR Invoice Header].[InvoiceDate], "yyyy-mm")

[TBC: confirm Text() is supported with Excel-style format codes. If not, the equivalent is:]

Year([Measures].[AR Invoice Header].[InvoiceDate]) & "-" & Right("0" & Month([Measures].[AR Invoice Header].[InvoiceDate]), 2)

Either way, an invoice dated 14 March 2026 now has an Invoice Month of 2026-03.

[Screenshot: Calculated Fields dialog with the InvoiceMonth expression]

3. Strip out the detail. Remove everything from the Down Axis except Customer and Customer Name.

4. Pivot the new field across. Add Invoice Month to the report and pivot it to the Across Axis, using the right click menu or the Rows / Columns dialog (see 02.03 Layout - Pivoting Across). Trim the Measures filter down to just the value you want, for example Line Amount.

5. Add totals. Add a Grand Total column (on the Column Totals tab of the Totals & Subtotals dialog) for the twelve month total, and a Grand Total row for the monthly totals.

[Screenshot: Sales by Customer by Month, months across]

Why "yyyy-mm"? Writing the label year first, with a two digit month, means the columns sort into the right order naturally: 2025-11, 2025-12, 2026-01. A label like "Jan 2026" would sort alphabetically, putting April before January. [TBC: confirm how calculated field members are sorted when pivoted across]

More Ideas

Once you have the idea, the same technique works for any period and any date:

Period

Example expression [TBC: check syntax]

Example result

Year

Year([InvoiceDate])

2026

Quarter

Year([InvoiceDate]) & "-Q" & RoundUp(Month([InvoiceDate]) / 3, 0)

2026-Q1

Month

Text([InvoiceDate], "yyyy-mm")

2026-03

Week

Year([ShipDate]) & "-W" & Right("0" & IsoWeekNum([ShipDate]), 2)

2026-W11

Day of week

Text([InvoiceDate], "ddd")

Mon

(Field names shortened for readability. In practice use the full field reference picked from the expression builder.)

And because the date is your choice, the same approach answers quite different questions:

You can even combine this with the techniques in 05.05, for example using Coalesce() to fall back to the Promised Ship Date when the Actual Ship Date is blank, and then converting the result to a month.

Things to Be Aware Of

Method 2: Date Bucketing

Calculated fields are perfect when the question is "which month was it in?". But some of the most common time based questions aren't about the calendar at all. They are about how old something is, or how far away it is.

The Classic Case: Aged Debt

Think about an aged debt report: one row per customer, with columns for Current, 1-30 days, 31-60 days, 61-90 days and over 90 days overdue.

These columns are not calendar periods. They are ranges of days measured relative to a date, usually today, or a month end "As Of" date. And as time moves on, the same invoice moves from one column to the next, without its due date ever changing.

You could attempt this with a calculated field, using Days() to work out how overdue each invoice is (as in 05.05) and a set of nested If() statements to band the result. But it quickly becomes cumbersome, and it shares the weakness noted above: a band with nothing in it simply disappears from the report. In an aged debt report you want every column to appear every time, even if it is empty.

Enter Bucketing

This is exactly what Date Bucketing is designed for. A bucketing configuration creates a new dimension whose members are time ranges ("buckets"), each defined by a duration, measured from a starting date that can be today, a fixed date or an As Of date. Every transaction falls into the bucket its chosen date lands in, and every bucket is always shown.

The same idea works well beyond aged debt, for example:

[Screenshot: Aged Debt report by Customer]

Bucketing also offers Monthly and Weekly presets. Because buckets are measured in days from a starting date rather than by the calendar, these are a little more fiddly for calendar month reporting than the calculated field approach above. But for weekly or daily projections forwards from today, they work very well.

We'll look at how to set up bucketing in detail in the next tutorial, 05.08 Date Bucketing.

Which Method Should I Use?

Calculated Fields

Date Bucketing

Best for

Calendar periods: years, quarters, months, weeks

Ageing and "relative to today" analysis

Periods defined by

The calendar, via a formula

Durations in days from a starting date

Empty periods

Only periods with data appear

Always shown

Moving through time

Combine with a dynamic date filter

Starting date can be today or an As Of date

Classic example

Sales by Customer by Month

Aged Debt

As a rule of thumb: if the question is "which month was it in?", use a Calculated Field. If the question is "how old is it?", use Bucketing.

Summary

Exercises

Now try the downloadable exercises below:

[05.07 Summarising Transactions into Time Periods - Exercises.pdf - placeholder]

Draft exercises:

  1. Create an InvoiceMonth calculated field on an AR Invoice Lines report, and build a Sales by Customer by Month report for the last 12 months.

  2. Change your report from exercise 1 to show quarters instead of months.

  3. Build a Shipments by Month report from Sales Order Lines. Which date did you use, and why?

  4. (Stretch) Using Coalesce(), create an "Effective Ship Month" that uses the Actual Ship Date where available and the Promised Ship Date otherwise, and use it to show past shipments and future commitments in a single report.

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

Powered By