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

Date hierarchy monthly drilldown has months out of order

Using PowerBI-Desktop to author a report.  I have monthly data for May 2015 through January 2016.  I started with a daily view and got a request for monthly view as well.  Seems to match what Date Hierarchy does so I switched to that.  The Year view seems ok but the problem is when you drill down I was expecting months to be ordered by year/month but instead it's simply month alphabetical.  So the columns in the chart left to right are January 2016, May 2015, June 2015, etc.  I've been looking to find a solution to specify ordering of the X-Axis but haven't found a solution.

11 REPLIES 11
Highlighted
Resident Rockstar
Resident Rockstar

Re: Date hierarchy monthly drilldown has months out of order

Highlighted
Microsoft
Microsoft

Re: Date hierarchy monthly drilldown has months out of order

Ok I tried that and I created a chart to filter on a column that is different than the Axis column.  I see how that works although since you can pick any used column it's a bit fuzzy at first that you need to pick the axis column.   Makes sense once you know.  This doesn't solve my current issue.

 

The chart is already sorting by the right date/time column.  I have a start_time column which is a SQL Server datetime type.

 

When I use the start_time as axis the chart is good.  It's daily data so it's a bit busy but correct.

 

When I change the axis from the start_time to "Date Hierarchy" I get mixed results.

Year view looks fine.  Sorting left to right is correct.

Quarterly is wrong because year isn't taken into consideration in sorting the months.  It's sorted Jan-Dec.  Q1 (far left) shows 2016 data, rest to right shows 2015 data.

Month view is wrong because year isn't taken into consideration in sorting the months. January (far left) shows 2016 data, the next month is May through December which shows 2015 data.  Data collection started May 2015.

Daily view - not sure what data is showing.  No indication of month and it's not January. 🙂

 

It acts like Year/Month information is chopped off the data as the Date Hierarchy it drills up and down.

 

Thanks

 

Highlighted
Memorable Member
Memorable Member

Re: Date hierarchy monthly drilldown has months out of order

@scottkon The auto Date Hierarchy is not a natural one. That means when you drill down to all levels ( double arrow ) it shows all months Jan to Dec sorted only by month and sums the data from all years. In your case it is strange since you start having data on May 2015.

 

                                                  Jan      .....May ....    Dec
contains and sums Data from                          2015       2015          2015
                                                      0                x                x
                                                      2016       2016         2016
                                                      x               0               0     

 

You can drill down to a specific year ( one arrow - right upper corner ) or better create a 2 Caculated columns one for Year  and one for YearMonth . Then add them to the chart axis

 

Year = YEAR(start_date)    


YearMonth =
YEAR (start_date) & " "
& LEFT ( FORMAT ( MONTH ( start_date); "MMMM" ); 3 )
 

 

Konstantinos Ioannou
Highlighted
Regular Visitor

Re: Date hierarchy monthly drilldown has months out of order

I have the same issue.  When the data goes across years it doesn't sort the date hierarchy correctly.  It makes the Date Hierachy feature useless unless it is for only one year of data.  So rolling 12 months can't be done.  

Highlighted
Regular Visitor

Re: Date hierarchy monthly drilldown has months out of order

If you click on the 3 dots at the top right corner of your visualisation, you'll find the option to "Sort by Year Month".  That should solve your sorting issue.

 

However I also see the problem when you drill down by month (without year), it groups values regardless of the year for all of January, all of December, and so on.  There should be a way to eliminate this grouping.

Highlighted
Regular Visitor

Re: Date hierarchy monthly drilldown has months out of order

If you click on the 3 dots at the top right corner of your visualisation, you'll find the option to "Sort by Year Month".  That should solve your sorting issue.

 

However I also see the problem when you drill down by month (without year), it groups values regardless of the year for all of January, all of December, and so on.  There should be a way to eliminate this grouping.

Highlighted
Frequent Visitor

Re: Date hierarchy monthly drilldown has months out of order

Does anyone have a fix for this issue? I also have the problem that I cannot start a timeline in one year and end in the next....

Highlighted
Advocate I
Advocate I

Re: Date hierarchy monthly drilldown has months out of order

I am also having this issue.  The "Date Hierarchy" is absolutely useless unless all of your data comes from the same calendar year.

 

In my case, our company has a fiscal year that runs from October-September but I still want October, November, December 2016 to show before 2017 when you drill down by month.

 

Come on, this should not be difficult.

Highlighted
Frequent Visitor

Re: Date hierarchy monthly drilldown has months out of order

This is the same situation as handling Date hierarchies in a cube. Remember when designing a Calendar dimension in SSAS cubes, we'd create a YYYYMM column so that the Date hierarchies would work correctly when used in a report? You'll need to use the same concept here to get the correct order of the date hierarchy in your drilldown. Just use the "Sort By" feature during the data import process along with the calculated YYYYMM column. That should resolve your issue.

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