Accounts Receivable Data Model

Modified on Fri, 21 Aug at 5:06 AM

Model Summary

The QQube Accounts Receivable Data Model analyzes open receivables, applied amounts, aging, payment behavior, and customer credit activity as of the date selected in the QQube Configuration Tool.

Model Type	Accounts receivable and aging analysis
Transaction Coverage	Linked posting and non-posting transactions
Aging Basis	Transaction Date and Due Date
Primary Use	Analyze balances, aging, payment behavior, and customer credit activity

Best Used For

Aging receivables by Transaction Date or Due Date.
Creating traditional 30-60-90 aging reports.
Comparing average days to pay with customer terms.
Reviewing balances by customer, job, class, territory, or sales representative.
Analyzing customer notes and credit standing.

Key Capabilities

Includes linked posting and non-posting transactions.
Provides Transaction Date and Due Date aging in the same model.
Includes original, applied, and open receivable amounts.
Includes printed and base units of measure.
Includes home and foreign currency values.

QuickBooks Data Limitations

Accounts Receivable aging is not dynamic.

Due to limitations in the QuickBooks data extraction process, enter the required As Of Date in the QQube Configuration Tool and run a synchronization to retrieve the appropriate aging data.

Quantity, Rate, Amount, and Aging Fields

Native fields come from QuickBooks Accounts Receivable data. QQube field extensions add printed-unit values for analysis.

Native Fields (20)

Quantity and Rate Fields	Amount Fields	Aging Fields
Line Quantity Line Rate Line Rate Percent Line Exchange Rate	Line Original Amount Line Original Amount (Foreign) Line Applied To A/R Amount Line Applied To A/R Amount (Foreign) Line Open A/R Amount Line Open A/R Amount (Foreign)	Txn Date Aging Current Txn Date Aging Bucket1 Txn Date Aging Bucket2 Txn Date Aging Bucket3 Txn Date Aging Bucket4 Due Date Aging Current Due Date Aging Bucket1 Due Date Aging Bucket2 Due Date Aging Bucket3 Due Date Aging Bucket4

QQube Field Extensions (2)

Quantity Fields	Rate Fields	Amount Fields
Line Quantity [Printed]	Line Rate [Printed]

Power BI and Excel Power Pivot Measures

The prepared Accounts Receivable examples include 31 visible DAX measures. Hidden measures are not included.

Measure Section	Visible Measures
Power BI Company-Filter Measures	3
.Base Measures	22
Average Days Measures	2
Calendar Pattern Measures	4

Power BI company filtering

The Account, Class, and Customer slicers use bridge tables and three support measures to show only values associated with the selected company. Excel Power Pivot handles this filtering automatically.

Included Dimensions

Calendars

Billed Date	Due Date	Service Date
Ship Date	Suggested Discount Date	Transaction Date
Job End Date	Job Projected End Date	Job Start Date
Calendar Overview

Document

Document Attributes

Line Attributes

Geography

Lists

Account	Class	Company
Currency	Customer	Customer Rep
Customer Message	Customer Sales Tax Code	Employee
Item	Item Sales Tax Code	Job
Job Rep	Other Name	Sales Rep
Sales Tax Item	Ship Method	Template
Terms	Vendor

Linked Information

A/R Linked Transactions

Technical Details

Model Structure	One Accounts Receivable FACT table with related dimensions and relationships already included
As Of Date	Selected in the QQube Configuration Tool before synchronization
Aging Behavior	Fixed to the synchronized As Of Date; not dynamically recalculated
Prepared DAX Measures	Power BI and Excel Power Pivot examples

Expected Result

Enter the required As Of Date, run a synchronization, and open the prepared example.

The FACT table, dimensions, relationships, fields, QQube extensions, and measures are already included.