Managing multiple date dependencies in Tableau

Factoring portfolio analysis approach

Introduction

In the factoring industry, portfolio metrics represent the lifeblood of business intelligence. Unlike simple transactional reporting, factoring operations require tracking receivables across multiple temporal dimensions—from the moment an invoice is purchased through its eventual payment. This multi-date dependency creates a unique challenge in business intelligence tools like Tableau: how do you calculate metrics that exist in a state between two dates?

The core challenge: portfolio as a time-series metric

Portfolio size in factoring isn’t a simple aggregate—it’s a temporal calculation. At any given point in time, the portfolio consists of all receivables that have been purchased but not yet fully paid. This creates a dependency on two critical dates:

  • Acquisition Date: When the receivable was purchased
  • Settlement Date: When full payment was received The fundamental logic can be expressed as:
Portfolio Size (at date D) = Sum of all outstanding amounts where:
  - Acquisition Date ≤ D
  - Settlement Date > D (or is NULL)

For monthly reporting, we typically measure portfolio size as of the last day of each month. However, implementing this seemingly straightforward logic in Tableau requires careful data modeling.

The scaffolding approach: building a date spine

The solution lies in creating a “date spine” or “calendar scaffold”—a comprehensive table of dates that serves as the temporal framework for our analysis. This approach transforms the problem from calculating snapshots to creating a relationship that naturally produces the correct temporal slices.

This approach has been refined through years of practical implementation in real-world factoring operations, balancing analytical power with performance and maintainability.

Creating the calendar table with custom SQL

Rather than maintaining an external Excel file, you can generate a calendar table directly in your data source using custom SQL. This keeps your data model self-contained and easier to maintain. Here’s an example for PostgreSQL:

WITH RECURSIVE date_spine AS (
  SELECT DATE '2020-01-01' AS calendar_date
  UNION ALL
  SELECT (calendar_date + INTERVAL '1 day')::DATE
  FROM date_spine
  WHERE calendar_date < DATE '2030-12-31'
)
SELECT 
  calendar_date,
  DATE_TRUNC('month', calendar_date)::DATE AS month_start,
  (DATE_TRUNC('month', calendar_date) + INTERVAL '1 month - 1 day')::DATE AS month_end,
  EXTRACT(YEAR FROM calendar_date)::INTEGER AS year,
  EXTRACT(MONTH FROM calendar_date)::INTEGER AS month,
  EXTRACT(DAY FROM calendar_date)::INTEGER AS day,
  EXTRACT(DOW FROM calendar_date)::INTEGER AS day_of_week
FROM date_spine;

This creates a comprehensive date dimension with useful attributes for filtering and grouping. You can add this as a data source in Tableau alongside your receivables fact table.

Establishing the relationship

In Tableau’s data model, you’ll connect your receivables fact table with the calendar dimension. Using Tableau’s relationship model (rather than traditional joins) provides more flexibility and allows Tableau to query at the correct grain dynamically.

Data model structure:

  • Fact Table: Receivables/Invoices containing acquisition dates, settlement dates, maturity dates, and amounts.
  • Calendar Dimension: Date spine created via custom SQL.

Relationship conditions:

Establish a relationship between these tables with calculated conditions:

  1. DATETRUNC('month', [Acquisition Date]) <= DATETRUNC('month', [Calendar Date])
  2. DATETRUNC('month', MAX(IFNULL([Settlement Date], TODAY()), [Maturity Date])) >= DATETRUNC('month', [Calendar Date])

The inclusion of Maturity Date in the second condition serves dual purposes: it ensures we can calculate delinquency metrics, and it provides an upper bound for receivables that remain unpaid. This relationship structure ensures that each receivable appears in the dataset for every month it was outstanding, creating the temporal expansion necessary for accurate portfolio tracking.

Building the portfolio calculation

With the calendar scaffold in place, the portfolio calculation becomes straightforward:

Portfolio Outstanding = 
IF [Acquisition Date] <= [Month End]
   AND ([Settlement Date] > [Month End] OR ISNULL([Settlement Date]))
THEN [Principal Amount]
END

For month-end snapshots specifically, you can filter to show only unique month-end dates from your calendar table.

Calculating days past due

The delinquency calculation requires comparing the month-end date against both the maturity date and settlement date:

Days Delinquent = 
IF [Maturity Date] >= [Month End]
   THEN 0
ELSEIF [Month End] > [Settlement Date] 
   THEN [Settlement Date] - [Maturity Date]
ELSEIF NOT ISNULL([Settlement Date]) AND [Settlement Date] > [Month End]
   THEN [Month End] - [Maturity Date]
ELSEIF NOT ISNULL([Settlement Date]) AND [Settlement Date] <= [Month End]
   AND [Settlement Date] > [Maturity Date]
   THEN [Settlement Date] - [Maturity Date]
ELSE [Month End] - [Maturity Date]
END

This logic handles multiple scenarios:

  • Receivables not yet due
  • Receivables paid while delinquent
  • Currently delinquent receivables
  • Receivables paid on time

Aging buckets

With days delinquent calculated, aging analysis becomes a simple bucketing exercise:

Aging Bucket = 
IF [Days Delinquent] < 1 THEN "Current"
ELSEIF [Days Delinquent] <= 30 THEN "1-30 Days"
ELSEIF [Days Delinquent] <= 60 THEN "31-60 Days"
ELSEIF [Days Delinquent] <= 90 THEN "61-90 Days"
ELSEIF [Days Delinquent] <= 120 THEN "91-120 Days"
ELSE "Over 120 Days"
END

This creates the standard aging report familiar to any credit professional.