Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
scottkon
Employee
Employee

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.

17 REPLIES 17
Adhirath
Frequent Visitor

my problem got solved, apparently if my date table has any 1 to 1 connection the date hierarchy doesn't activate.
Let me know if this helped.

Adhirath
Frequent Visitor

Hi, try this and let me know if it works.
make two columns, one month name and the other month number.
choose month name as the rows,
then go to model view and in properties go to advanced then sort by column as month number. I dont know how good this will work with multiple calender years thouhg. Best way I know is using built in date hierarchy but mine just suddenly stopped working for some reason. let me know f someone knows how to fix that, i have aut date/time checked and no table is marked as date table.

JustSomeDude
New Member

Hello,

I know that this is an old topic but the easiest solution that I found for this problem is to create a seperate day/date table within any PBI report that has a colume for each date data type. THEN... create hierachies within that day/date table.

If you've got just a list of dates in some table in your report, you link it to the PK_Date field in your day/date table, then if you want to drill down, you can just throw in years, months, dates, quarters, weeks, basically whatever you want, and you can drill up and down through them because they will already be ordered in your table.

Hope that helps.

carlol
Helper IV
Helper IV

Did anybody find out how to fix this , tried all the suggestions I found

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.