A list of transactions is useful, but often the real question is a summary over time:
How much did each customer buy each month this year?
How many order lines are due to ship each week for the next quarter?
How much of our debt is 30, 60 or 90+ days overdue?
To answer questions like these we need to do two things:
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.
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:
Calculated Fields - very flexible, and ideal for calendar based summaries such as year, quarter, month or week
Date Bucketing - ideal for ageing and other analysis measured relative to a date, such as today
Here is a video, or read below - don't forget the exercises at the bottom!
[Video placeholder] - New module so not yet filmed
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.
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]
Once you have the idea, the same technique works for any period and any date:
Period | Example expression [TBC: check syntax] | Example result |
Year |
|
|
Quarter |
|
|
Month |
|
|
Week |
|
|
Day of week |
|
|
(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:
Shipments by month - Sales Order Lines on Ship Date
Purchases by supplier by quarter - AP Invoice Lines on Invoice Date
Stock movements by week - Inventory Transactions on Transaction Date
Orders taken vs shipped - two reports, one on Order Date and one on Ship Date
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.
Only periods with data appear. A calculated field only produces members for values that actually exist in the data. If a customer bought nothing in a month, that cell is simply blank, but if nobody bought anything in a month, there will be no column for that month at all.
Always filter on the underlying date. As noted in 05.05, filtering on a calculated field happens after the data is retrieved. So filter the date itself (ideally with a dynamic date filter) to keep the report fast, rather than filtering on the Invoice Month field.
GL already has periods. GL reports already have Fiscal Year and Fiscal Period dimensions, so this technique is mainly for subledger and operational data, or where calendar months differ from fiscal periods.
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.
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.
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:
Aged creditors - AP Invoices bucketed on Due Date
Forward order book - open Sales Order Lines bucketed on Promised Ship Date, looking forward week by week
Expected receipts - open Purchase Order lines bucketed on Due Date
[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.
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.
Summarising over time means stripping out detail and pivoting a time period across
Calculated Fields can turn any date into a year, quarter, month or week label that can be pivoted across
Use a year-first label format so the columns sort correctly
Filter on the underlying date, ideally with a dynamic date filter
For ageing and "relative to today" analysis, use Date Bucketing, covered next in 05.08
Now try the downloadable exercises below:
[05.07 Summarising Transactions into Time Periods - Exercises.pdf - placeholder]
Draft exercises:
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.
Change your report from exercise 1 to show quarters instead of months.
Build a Shipments by Month report from Sales Order Lines. Which date did you use, and why?
(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].