How to Sort Aging Buckets in Power BI

Creating an accounts receivable aging report is a fantastic use for Power BI, but you've probably run into a frustratingly common roadblock: your aging buckets are completely out of order. Instead of seeing a neat progression from "0-30 days" to ">90 days," your visuals are an alphabetical mess. This article will walk you through exactly how to fix this by creating a custom sort order, step-by-step, so your reports finally make sense.

Why Your Aging Report Sorts Incorrectly

So, you’ve built your aging report, your DAX measures are calculating perfectly, and you drop your aging buckets into a bar chart. But instead of showing "0-30," "31-60," "61-90," and so on, it displays something like "<0 days," ">90 days," "0-30 days," "31-60 days." What’s going on?

The problem is simple: Power BI sees your aging buckets as text strings, not numbers. By default, it sorts text alphabetically. "0-30 days" comes after ">90 days" for the same reason that "Orange" comes after "Grapefruit." Without a different instruction, Power BI has no way of knowing the logical, chronological order you want to see.

This is an extremely common issue when dealing with any custom categorical data like survey responses ("Strongly Agree," "Agree," "Disagree") or t-shirt sizes ("Small," "Medium," "Large"). The good news is that the solution is straightforward and gives you complete control over how your data is displayed.

The Solution: Create a "Sort By" Helper Column

To fix this, we need to give Power BI instructions it can understand. Since Power BI is great at sorting numbers, we'll create a second, hidden "helper" column that assigns a numeric value to each of your aging buckets. For example:

• "0-30 days" gets assigned the number 1
• "31-60 days" gets assigned the number 2
• "61-90 days" gets assigned the number 3
• ">90 days" gets assigned the number 4

Once this helper column exists, you can tell Power BI: "Hey, see this column of text-based aging buckets? I want you to sort it not by the text itself, but by the numbers in this new helper column." This forces the visual into the correct chronological order.

There are two primary places you can create this helper column: in the Power Query Editor during data transformation or with a DAX calculated column in the data model. We'll walk through both, starting with the most common and often preferred method: Power Query.

Method 1: Using Power Query's Conditional Column

Adding the sort column in Power Query is often the best practice. It happens early in your data transformation pipeline, keeping your DAX model cleaner and potentially improving performance on very large datasets. The Conditional Column feature makes this easy to do without writing any code.

Step 1: Open the Power Query Editor

First, you need to get into the Power Query Editor where all the data shaping happens. In Power BI Desktop, go to the Home tab on the ribbon and click on Transform data. This will open a new window showing your existing queries.

Step 2: Select Your Query and Add a Conditional Column

From the list of queries on the left, select the one that contains your aging bucket column (e.g., 'Invoices' or 'financial_data'). Now, with that query selected:

1. Navigate to the Add Column tab in the ribbon.
2. Click on Conditional Column. This will open an intuitive interface for building IF/THEN logic.

Step 3: Define the Logic for Your Sort Order

Now you'll see the "Add Conditional Column" dialog box. This is where you will associate each aging bucket with its sorting number.

1. New column name: Give your new column a clear name, something like "Aging Sort Order" or "Bucket Index." It's just for you, so make it obvious.
2. First condition: Under the If statement, select your existing aging bucket column name from the first dropdown (e.g., [Aging Bucket]). Set the operator to equals and then type the exact text for your first bucket in the Value box (e.g., "0-30 days"). In the Output box, type 1.
3. Add additional conditions: Click the Add clause button to create the next Else if line. Repeat the process for your other buckets, incrementing the output number each time.
4. Set a default value: The final Else box is a catch-all. It's good practice to enter a default value here, like 99, in case new, unexpected aging buckets appear in your data source later. This will make them easy to spot as they'll be sorted last.

Your finished dialog box should look neat and logical, clearly mapping each text label to a sequential number.

Step 4: Check the Data Type and Close & Apply

After you click OK, Power Query will add your new "Aging Sort Order" column to the end of your table. Double-check one crucial detail: make sure the data type is set to a number. You should see a "123" icon in the column header. If not, click the icon and change it to Whole Number.

Once everything looks good, click the Close & Apply button in the top-left of the Power Query Editor to load your changes back into the main Power BI data model.

Method 2: Using a DAX Calculated Column

If you prefer working with DAX or need to create the sort order after the data has already been loaded, a calculated column is a perfectly viable alternative. It accomplishes the same goal directly in your data model.

Using DAX can be quicker if you're already iterating in the report view. For this, the SWITCH() function is cleaner and easier to read than a series of nested IF() statements.

Step 1: Go to the Data View

In your Power BI Desktop file, click on the Data icon on the left-hand navigation pane (it looks like a small table).

Step 2: Create a New Calculated Column

Select the table that contains your aging buckets. In the Column tools ribbon that appears at the top, click New column. This will open the formula bar where you can enter your DAX expression.

Step 3: Write the SWITCH() Function

In the formula bar, paste and adapt the following DAX formula. This function evaluates each row and returns a corresponding number based on the text in your [Aging Bucket] column.

Be sure to replace 'YourTable'[Aging Bucket] with the correct name of your table and column.

Aging Sort Order = 
SWITCH(
    TRUE(),
    'YourTable'[Aging Bucket] = "0-30 days", 1,
    'YourTable'[Aging Bucket] = "31-60 days", 2,
    'YourTable'[Aging Bucket] = "61-90 days", 3,
    'YourTable'[Aging Bucket] = ">90 days", 4,
    99  // This is the default value if none of the above match
)

Press Enter to create the column. Just like with Power Query, a new column will appear in your table, populated with the numbers 1, 2, 3, 4, and 99.

The Final Step: Applying the Sort Order

You’ve created your helper column, but your charts are still scrambled. Why? Because you haven’t completed the final, crucial step: telling Power BI to use it.

1. Stay in the Data View (or go back to it).
2. Don't select the new numeric sort column. Instead, click on the header of the original text-based aging bucket column (the one you want to sort, like [Aging Bucket]).
3. With that column selected, the Column tools ribbon will activate at the top.
4. Find the Sort by column button in the "Properties" section of the ribbon.
5. Click it, and from the dropdown menu, select your newly created helper column (Aging Sort Order).

Nothing will visibly change in the data table, but Power BI has now memorized this sorting rule. Return to your Report view. Your bar charts, tables, matrices, and slicers featuring the aging buckets will magically snap into the correct chronological order. The alphabetic sorting is gone, replaced by the logical sequence you defined.

Final Thoughts

Fixing the default alphabetical sorting in Power BI is a matter of creating a simple helper column to provide the correct numeric order. Whether you use Power Query's user-friendly conditional column interface or a quick DAX SWITCH function, the key is to apply that helper column's logic to your original category text field using the "Sort by column" feature.

Learning how to handle little productivity blockers like this is a big part of mastering data reporting. Spending hours fighting with tool configurations is a frustrating reality for many teams that just want clear answers from their data. At Graphed, we created our AI data analyst specifically to eliminate that friction. Instead of manually structuring data, setting up sorting rules, and wrestling with visuals, you can just connect your data sources and ask questions in plain English - like "create a chart of open invoices in aging buckets" - and get a perfectly sorted, real-time visualization in seconds.