How to sort chart by date columns in Power BI?

by

Here’s a question from Eugene:

I need to create a chart that displays some business related KPI by month ina Power BI report. Problem us that the chart x axis is not correctly ordered and the month names are displayed in some random order (nor ascending nor descending). Is there a simple solution to display the month order in a correct fashion (nor alphabetically nor by any measure).

Sort chart axis values by another column

Summary

To display a chart axis in the chronological order make sure to sort the axis variable by an ordered column. For example – to display month names chronologically, create a month name column and sort the month name column by it.

Import Dataset to Power BI

We will first go ahead and import the following data into our Power BI report. Note the column header names as we will use them throughout the tutorial.

As you can see for each date we have a Month name column, as well as a random number of interviews executed as part of this candidate campaign.

Rendering our bar plot visual

We can render a simple bar chart using the data above. Here’s the result:

Create an hidden Month Number column

As you can see, the month names are randomly ordered and can’t be sorted according to the chronological order.

Therefore, we will now proceed and create a Month number column:

  • Open the Data View.
  • Hit the New Column button.
  • Use the following DAX expression to create the column:
Month Number = Month (Interviews[Date])
  • Hit Enter – you will see that the Month Number column was populated with the relevant values.
  • Go the Data Pane in the right.
  • Right click the Month Number column and select Hide in report view.
  • Hit Save.

Sort your chart by hidden column

  • Return to the Report view.
  • In the Data tab, highlighted the Month Name.
  • Then under the Column Tools tab hit the Sort by Other column button.
  • From the Dropdown, select Month Number.
  • Now look into your bar plot – if the X- axis is not ordered correctly, hover on the top right side of the visual and hit the More Options ellipsis (three dots).
  • Select the Sort Axis option and pick Sort Ascending.
  • You will be now all set: