cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
scottkon
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.

13 REPLIES 13
AP_BI
Frequent Visitor

Hello community, 

I am looking for a solution on this topic for a while now and actually did not think that it is so difficult but as this post shows, the answer on this is not so easy at all.

I think this screenshot describes best what the problem is (on the next hierarchy level including month names I observe the same problem)

AP_BI_0-1606918814690.png

Another curious observation is that - depending on slicer selection at the top of the report - the date hierarchy is sometimes sorted correctly and sometimes not. So obviously PBI is capable of sorting in the right manner. 

I really would appreciate your help!
KInd regards,
Andreas

luigi_dgv
Frequent Visitor

In the chart area, once the hierarchy is on the axis, drill up to the least granular level.
Then on the top left corner click the icon representing a bi-forked arrow ("Expand all down one level in the hierarchy"). That will concatenate year and month in the proper way on the axis, maintaining the correct (date-based) sorting.

 

@luigi_dgv ,

No, it does not.

Even if the dim table is sorted in the correct order, months are sorted alphabetically.

Some answers say to adjust the sort order at each level in the hierarchy but, how??  This information is not coded in our DNA...

The sort by column is disabled when you select the hierarchy level so, how to sort?

 

Turns out you need to select the actual field (not the version in the hierarchy) and the sort option then becomes active.  If you select Month (text version) then click on Sort by Column in the Modelling tab and select Month Number (you need to include this in your Date table), then it will work.

Your solution sounds best beside the idea of creating YYYYMM column.

I know it is a very old post but could you explain how it exactly works and what to set in PBI Desktop? 

MuqadderIqbal
Frequent Visitor

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.

ryanjlind
Advocate I
Advocate I

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.

Ash
Regular Visitor

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.

RobFlanders
Frequent Visitor

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....

Ash
Regular Visitor

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.

kbennett
Regular Visitor

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.  

greggyb
Resident Rockstar
Resident Rockstar

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

 

@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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.