How much do your customers owe you? How about customers with payments long overdue? How much advance payments do you have in hand? For all these questions, the Customer Balance Report provides you with the answer. This week we'll see how to create a customer balance report based on your transactions over a specific period.

You can create this report over Zoho Books or Zoho Finance data.

To create a customer balance report, you need to follow the below steps.

• Define the period for analysis (by creating a Query Table)
• Get the Opening balance, Debit and Credit (by creating a Query Table)
• Join the above two Query Tables 
• Create the Customer Balance Report on top of the above joined table
• Get Closing Balance (by defining a Report Formula)

Define the period for analysis  (Query Table)

Define the period for which you want to analyze the customer balance. The following query will set the period as from the beginning of the year till the current date. 

Get Opening balance, Debit and Credit (Query Table)

Now you need to get the opening balance for each customer at the beginning of the period, and debits and credits during the period. The following query gets these details. 

Join Tables with Customer Data

Join the query table with your customer data to get the above details for each customer. 

Create Customer Balance Report

Create a new Pivot View and add columns as follows.

• Row - Customer Name for Customers table. 
• Data - Opening Balance, Debit and Credit from Customer Balance query table.

Get Closing Balance (Report Formula)

To complete the customer balance report, you need to find the Closing Balance. Create a Report Formula to get this. Click the formula icon and enter the below formula. 

Report will be generated as follows. Apply the required theme. 

The following is the final report.