The Accounts Receivable Dimensional Model
Managing corporate liquidity requires continuous visibility into outstanding customer invoices. An enterprise Accounts Receivable (AR) analytics model must reconcile open sub-ledger invoices against general ledger balances while categorizing invoices into standard payment aging brackets (Current, 1–30 Days, 31–60 Days, 61–90 Days, and 90+ Days Past Due).
[DimCustomer] (Customer Details, Credit Terms, Risk Tier)
│
▼ (1:N)
[FactInvoices] (InvoiceId, InvoiceDate, DueDate, Amount, ClosedDate)
▲
│ (1:N)
[DimDate] (Calendar, Fiscal Periods, Working Days)
│
[AgingBuckets] (Disconnected Configuration Table: Bucket, MinDays, MaxDays)Dynamic Aging Measures in DAX
Rather than hardcoding aging buckets into database tables during ETL—which prevents retroactive reporting on what aging looked like on a past date—use DAX to evaluate open balances dynamically based on the report slicer date:
// Calculate Outstanding Receivables Balance as of Selected Report Date
AR_OutstandingBalance =
VAR AsOfDate = MAX('DimDate'[Date])
RETURN
CALCULATE(
SUM('FactInvoices'[InvoiceAmount]),
'FactInvoices'[InvoiceDate] <= AsOfDate,
'FactInvoices'[ClosedDate] > AsOfDate || ISBLANK('FactInvoices'[ClosedDate])
)
// Dynamic Aging Bucket Allocation
AR_Balance_In_Bucket =
VAR AsOfDate = MAX('DimDate'[Date])
VAR MinDays = SELECTEDVALUE('AgingBuckets'[MinDays])
VAR MaxDays = SELECTEDVALUE('AgingBuckets'[MaxDays])
RETURN
CALCULATE(
[AR_OutstandingBalance],
FILTER(
'FactInvoices',
VAR DaysPastDue = AsOfDate - 'FactInvoices'[DueDate]
RETURN
DaysPastDue >= MinDays && DaysPastDue <= MaxDays
)
)Days Sales Outstanding (DSO) Countback Method
Days Sales Outstanding measures the average number of days it takes for a company to collect payment after a sale. The standard simple formula (Total AR / Total Credit Sales) * Period Days assumes uniform sales throughout the quarter. For businesses with seasonal revenue spikes, the Countback Method is the recognized standard:
// Days Sales Outstanding Countback Pattern
DSO_Countback =
VAR CurrentAR = [AR_OutstandingBalance]
VAR Month1Sales = CALCULATE(SUM('FactSales'[GrossSales]), PREVIOUSMONTH('DimDate'[Date]))
VAR Month2Sales = CALCULATE(SUM('FactSales'[GrossSales]), PARALLELPERIOD('DimDate'[Date], -2, MONTH))
RETURN
IF(
CurrentAR <= Month1Sales,
DIVIDE(CurrentAR, Month1Sales) * 30,
30 + DIVIDE(CurrentAR - Month1Sales, Month2Sales) * 30
)