cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Sorting bar chart chronologically when using date hierarchy

I've seen a few posts on this, but nothing I could find to answer this specific concern. I have daily data from Dec-19 through Mar-20 that is in a bar chart. End users would like for it to be grouped by month. When I use the date hierarchy to group by month (I include day as well so they can drill down to daily view for each month), it puts December at the end of the bar chart, even though chronologically it should be first. WITHOUT adding another column or measure, is there a way to keep this data sorted chronologically? I'm not necessarily opposed to adding a column/measure, but I feel like this is a pretty easy task for Power BI and I'm just not familiar with the setting - feel like I shouldn't have to add more data to keep the underlying chronological data in chronological order. 

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Sorting bar chart chronologically when using date hierarchy

@mterry , if you are using date hierarchy than drill down should have given right sorting

 

We typically use date table and column-like

Month Year = FORMAT([Date],"mmm-yyyy")

or

Month Year = FORMAT([Date],"mmmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")

 

And we mark month year sort as sort column

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Sorting bar chart chronologically when using date hierarchy

This is typically achieved when you have a Date table with the month number and year number concatenated e.g. 122019 then you proceed to select the column and sort via the option avalaibe in the columns tools in the ribbon

Highlighted
Helper II
Helper II

Re: Sorting bar chart chronologically when using date hierarchy

Data Pane > Select "Date" table > Select "Column" for month > Ribbon "Column tools" > Sort by column, dropdown, select the right column of your interest.

 

Go back to chart, it should automatically fix it.

(if not delete and add the column to the chart again)

 

- SV

Highlighted
Helper IV
Helper IV

Re: Sorting bar chart chronologically when using date hierarchy

I attempted to sort both the date column, and the month column by the period column (i.e. 201901) - when I attempt to do that with the date column, the diplay gives me an error. When I attempt to sort the month column by the period I get an error saying 'We can't sort the 'MonthName' column by 'Period'. There can't be more than one value in 'Period' for the same value in 'MonthName'.....

Highlighted
Helper II
Helper II

Re: Sorting bar chart chronologically when using date hierarchy

The error is caused because there are more than 1 values, in your case. 

             i.e., Display Column Values to Sort Column Values has to be 1-to-1 mapping.  
Data can be duplicated but strict 1-1 mapping.

 

Please check Periods - MonthName values ... or post the screenshot of the data

 

Month Periods (Display)      YYYYMM (Sort)

JAN 2019            201901

FEB 2019             201902

 

Date (Display)      YYYYMMDD (Sort)

01-Mar-2019      20190301

02-Mar-2019      20190302

 

- SV

 

Highlighted
Helper IV
Helper IV

Re: Sorting bar chart chronologically when using date hierarchy

Okay, so these all seem to be work arounds, not necessarily addressing the original issue I had. If I use the date column (individual date) on the axis, and let Power BI use the date heirarchy, using only month, then there is no way to segregate the months across years without including the years value and drilling, correct?

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors