Have you ever created a chart using a date component and the dates are completely out of order? In this blog, we discuss why this happens and how to fix this.  

In the example below, Power BI is sorting the chart alphabetically by month name instead of chronologically.

‍

‍

This typically occurs when you leverage a date field from a date dimension table. In this example, Power BI is unable to determine the appropriate order for the months because “month name” is a string field. Power BI lacks the inherent knowledge of the chronological order of the months, such as which month comes before or after July.

To resolve this, you must tell Power BI which field to use as an index for the “month name” field. Fortunately, this is a straightforward process:

1. Select the “month name” attribute
2. Ensure that the “column tools” tab is active
3. Click on “sort by column”
4. Specify the “month” field as the indexing field

‍

‍

The “month” field corresponds to the numerical representation of each month, with July being the seventh month and August being the eighth, for example. By linking the “month name” field to the “month” field, Power BI gains the necessary knowledge to sort the months correctly.

After Power BI loads this change, you can see that your chart is now sorted properly. If the issue persists and the order still appears incorrect:

1. Make sure that chart sorting is based on the date field
2. If the values are chronologically correct but starting from the wrong point in time (eg. Dec -> Jan instead of Jan -> Dec), simply change the sort from ascending to descending or vice versa depending on your situation

‍

‍

What if you don’t have an index field? There are many ways to create indexes. Covering each method is out of scope for this article but for this example, we created the index in Power Query with one simple line of m-code. Once you have your index field created, simply follow the steps we have outlined above to correctly sort your visual.

‍

‍

In summary, when Power BI does not understand how our data should be sorted, you need to do the following:

1. Create an index field (skip if already created)
2. Select the field that needs to be sorted
3. Click ‘Sort By’ and select the related index field