Forum Discussion
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
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
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
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
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'.....
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
