Customer Aging Dashboard
In this section, you’ll learn how to turn data into an interactive Customer Aging Dashboard.
A Customer Aging Dashboard is a tool used in businesses to track and manage how long customers take to pay their invoices (bills). It shows a clear breakdown of outstanding payments (unpaid bills) based on time periods, such as 0-30 days, 31-60 days, 61-90 days, and more than 90 days. This helps businesses understand which customers are paying on time and which are delaying payments, think of it as a way to organize and visualize unpaid bills, grouped by how overdue they are.
It highlights potential risks like late-paying customers, helping businesses decide on follow-up actions, such as sending reminders or offering payment plans. It’s like a progress tracker for money that a business is owed, ensuring smoother cash flow and better financial planning.
Try it yourself first! Build the dashboard and, if you get stuck, refer to the course video for help.
1. Connect Power BI to your data source, download this Excel Customer Aging Dataset
2. Understand Tables of your data set, we have three tables in data set.
The Customer Aging Dashboard is based on the following data tables:
1. Database Sheet
This is the primary dataset containing detailed information about invoices and customer payments. Key columns include:
• Invoice No.: Unique identifier for each invoice.
• Invoice Date: The date the invoice was issued.
• Customer: Name of the customer associated with the invoice.
• Payment Terms: Agreed terms for payment (e.g., 30 days).
• Due Date: The deadline for payment.
• Overdue Balance: The amount that is overdue.
• Age Bracket: Classification of overdue payments (e.g., 0-30 days, Above 360 days).
• Sales Person: The salesperson responsible for the customer.
• City: Location of the customer.
• Customer Type: Whether the customer is a retailer, dealer, or OEM.
2. Aging Slabs Sheet
This sheet defines the time ranges (or “slabs”) used to classify overdue payments. Key columns include:
• Column1: Start of the time range (e.g., 0, 31, 91).
• Column2: Description of the aging slab (e.g., “0-30 days”, “Above 360”).
3. Setup Sheet
This sheet contains metadata or setup information about customers. Key columns include:
• S. No.: Serial number for identification.
• Customer: Name of the customer.
• City: Location of the customer.
• Category: Type of customer, such as OEM or Retailer.
4.Date Table
A Custom Date Table is a structured table in Power BI that provides detailed information about dates and time periods. It is essential for time-based reporting. A custom date table can be created using DAX in Power BI. In Power BI Desktop navigate to Home and click on New Table and paste below DAX code and click commit sign.
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2019, 1, 1), DATE(2023, 12, 31)),
“Year”, YEAR([Date]),
“Month Number”, MONTH([Date]),
“Month Name”, FORMAT([Date], “MMMM”),
“Quarter”, “Q” & FORMAT([Date], “Q”),
“Week Number”, WEEKNUM([Date]),
“Day”, DAY([Date]),
“Day of Week”, WEEKDAY([Date]),
“Day of Week Name”, FORMAT([Date], “dddd”),
“Is Weekend”, IF(WEEKDAY([Date], 2) >= 6, “Weekend”, “Weekday”)
)
Data Relationships
The following relationships define how the tables are connected:
Database to Setup:
• Join Type: Many-to-One
• Key: Customer
• Description: Database Connects to the Setup using Customer, links customer information (e.g., name, contact details) to their invoices.
Database to Data table:
• Join Type: Many-to-One
• Key: Date
• Description: Database Connects to the Data Table using date links to invoice date of customer information (e.g., date, date of week)
Aging slab:
Description: Not directly linked but used for categorizing data in the Database or during calculations.
Create visualizations to display important information in charts, tables, and maps.
Your dashboard should match below layout.
Customer Aging Dashboard
