Aging reports are a common type of report that show the status of items such as invoices, payments, orders, or inventory over time.
They help businesses to monitor their cash flow, manage their inventory, and optimize their operations.
However, creating aging reports in Power BI can be challenging, especially if you want to allow users to select a different date and see how the aging report changes accordingly. That’s why many businesses rely on power bi consulting services to help them design and implement effective aging reports in Power BI.
In this article, I will show you how to create aging reports in Power BI that are dynamic, flexible, and informative.
What is an Aging Report?
An aging report is a report that shows the distribution of items by their age.
For example, an accounts receivable aging report shows how much money customers owe to a business and how long they have been overdue.
An accounts payable aging report shows how much money a business owes to its suppliers and how long it has been overdue.
An aging report typically uses bins or buckets to group items by their age.
For example, a common binning scheme for accounts receivable is 0-30 days, 31-60 days, 61-90 days, and over 90 days. These bins indicate how long an invoice has been outstanding since its due date.
How to Create Aging Reports in Power BI?
To create aging reports in Power BI, you need to have a data source that contains the following information:
- The item identifier (such as invoice number or order number)
- The item amount (such as invoice amount or order amount)
- The item due date (such as invoice due date or order due date)
- The item status (such as open or closed)
For this example, I will use a sample data set of accounts receivable that I downloaded from here. You can download the same data set and follow along with me.
The data set contains 1000 rows of invoices with the following columns:
- Invoice No: The invoice number
- Invoice Date: The invoice date
- Due Date: The invoice’s due date
- Amount: The invoice amount
- Paid Date: The invoice paid date
- Status: The invoice status (Open or Closed)
Here are the steps to create an aging report in Power BI using this data set:
Step 1: Load the Data into Power BI
The first step is to load the data into Power BI. You can do this by clicking on Get Data in the Home tab and selecting Excel. Then browse to the location where you saved the data set and select it.
Power BI will show you a preview of the data in the Navigator pane. You can select the sheet that contains the data (in this case, Sheet 1) and click on Load.
Power BI will load the data into the Data view. You can see the columns and rows of the data in the Fields pane and the Table pane.
Step 2: Create a Date Table
The next step is to create a date table that will be used to filter the data by a user-selected date. You can do this by clicking on New Table in the Home tab and entering the following formula:
Date = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))
This formula will create a table called Date that contains one column with dates from January 1, 2018, to December 31, 2020. You can adjust the start and end dates according to your data.
Also, you can add some additional columns to the data table, such as year, month, and quarter. You can do this by clicking on New Column in the Home tab and entering the following formulas:
Year = YEAR(Date[Date])
Month = FORMAT(Date[Date],”mmm”)
Quarter = “Q” & FORMAT(Date[Date],”Q”)
These formulas will create three columns called Year, Month, and Quarter that extract the year, month, and quarter from the date column.
You can also mark the date table as a date table by clicking on Mark as Date Table in the Modeling tab and selecting the date column as the date column.
Step 3: Create a Relationship between the Data Table and the Date Table
The next step is to create a relationship between the data table (Sheet 1) and the date table (Date) based on the due date column. You can do this by clicking on Manage Relationships in the Modeling tab and selecting New.
Power BI will show you a dialogue box where you can select the tables and columns to create the relationship. You can select Sheet 1 as the first table, Due Date as the first column, Date as the second table, and Date as the second column. You can also select Many to One (*) as the cardinality and Single as the cross-filter direction.
Power BI will create a relationship between the two tables and show it in the Relationships view. You can see a line connecting the two tables with an asterisk (*) on one end and one (1) on the other end.
Step 4: Create Measures for Aging Report
The next step is to create measures that will calculate the aging report based on the user-selected date. You can do this by clicking on New Measure in the Home tab and entering the following formulas:
Total Amount = SUM(Sheet1[Amount])
This measure will calculate the total amount of all invoices.
Selected Date = MAX(Date[Date])
This measure will return the maximum date from the date table, which is equivalent to the user-selected date.
Age = DATEDIFF(Sheet1[Due Date],[Selected Date],DAY)
This measure will calculate the age of each invoice in days based on the due date and the selected date.
Bin = SWITCH(TRUE(),
[Age] <= 30, “0-30”,
[Age] <= 60, “31-60”,
[Age] <= 90, “61-90”,
[Age] > 90, “>90”,
BLANK()
)
This measure will assign each invoice to a bin or bucket based on its age. You can change the binning logic according to your business needs.
Amount by Bin = CALCULATE([Total Amount],FILTER(Sheet1,[Bin] = SELECTEDVALUE(‘Aging Report'[Bin])))
This measure will calculate the total amount by bin or bucket based on the selected value from a slicer that we will create later.
Step 5: Create Visuals for Aging Report
The final step is to create visuals that will display the aging report in a clear and concise way. You can do this by clicking on New Page in the Report view and adding some visuals from the Visualizations pane.
For this example, I will create a matrix that will show the count and percentage by bin and bucket, and status.
To create a matrix, you can drag and drop the Bin measure from Sheet 1 into a blank area of the report canvas. Power BI will automatically create a matrix with bins as rows.
You can then drag and drop the Status field from Sheet 1 into the Columns well and the Count of Invoice No field from Sheet 1 into the Values well.
Power BI will create a matrix with bins as rows, status as columns, and count of invoices as values.
You can also add another measure to calculate the percentage of invoices by bin and status. You can do this by clicking on New Measure in the Home tab and entering the following formula:
% of Invoices = DIVIDE(COUNT(Sheet1[Invoice No]),CALCULATE(COUNT(Sheet1[Invoice No]),ALL(Sheet1[Status])),0)
You can change the format of the matrix by clicking on it and using the options in the Format pane. For example, you can change the font size and colour, add conditional formatting, and add a title. I hope you found this article helpful and informative. If you need any power bi consulting services or have any questions or feedback, please feel free to comment.
