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

Date order of bars in stacked bar chart

Hi,

 

I have a dataset that is a simple collection of stats per calendar month. The dataset looks like

 

PeriodMonth NumberLanding Page HitsWiFi SessionsData Download (GB)
Mar-181215569314441267260

 

The period rows runs from Mar-18 to Mar-19 (mmm-yy) formatted as a custom date format in Excel. I want to display the stats on a bar graph with the period displayed on the y-axis in increasing month order (oldest to newest) piece of cake in Excel not so in Power BI it seems. First problem is on import Power BI ignores the excel data type and imports the period as text and so when you come to graph the data it displays the period in alphabetical (Apr-18, Aug-18, Jly-18, Jun-18 and so on) rather than date order (Mar-18, Apr-18, May-18, Jun-18). It is not apparently possible to have the axis labels display values that are independent of the data being plotted and it is not possible to have the data sort based on any other data values in the dataset other than those being plotted (I added a month number to see if I could do this but no). If I edit the dataset in power BI before the import and force the conversion of Power BIs choice from text to a date then Mar-18 becomes 01/03/2018 which is what displays on the axis label which is inaccurate and not what I want to achieve.

 

So I am a bit stumped and would like to ask the community if anybody else has overcome this particular problem and if so how? I would like to ask microsoft to improve the functionality in this area to make it much more easier for users to perform what should be a relatively simple graphing exercise utilising dates over time.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Date order of bars in stacked bar chart

OK, I understand but the answer doesn't change. the way to fix this is in Desktop, select your data icon on the left (table icon). Select your table. Select your Period column. Select "Modeling" from the ribbon and then "Sort by Column", select your "Month Number" column. Then, use your Period column in your visual and it *should* sort by Month Number.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Date order of bars in stacked bar chart

Are you saying that you can't set your Sort By column to Month Number? Modeling tab Sor By.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

hobe1 Helper II
Helper II

Re: Date order of bars in stacked bar chart

Hi Greg,

 

Not exactly I am saying the only way I can get the bar graph to display the stats in calendar month order is to plot by month number but if I do that the axis labels read 1,2,3,4 etc which is not as intuitive as having a period label of Mar-18, Apr-18 etc along the axis label but if I plot period vs stats then power BI insists on seeing the period as a piece of text and sorts the period alphabetically which is no good. Ideally I would want to either select the order in which the different data points are ordered manually or be able to sort the axis data based on an independent parameter that was part of the dataset. So plot period vs usage stat and sort by month number. As it is I can see no easy way to plot time periods (calendar month-year) along an axis in date order. Does that answer your question?

Super User IV
Super User IV

Re: Date order of bars in stacked bar chart

OK, I understand but the answer doesn't change. the way to fix this is in Desktop, select your data icon on the left (table icon). Select your table. Select your Period column. Select "Modeling" from the ribbon and then "Sort by Column", select your "Month Number" column. Then, use your Period column in your visual and it *should* sort by Month Number.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

hobe1 Helper II
Helper II

Re: Date order of bars in stacked bar chart

Hi Greg,

 

I am somewhat embarassed to say I never spotted that option but thank you very much it has solved my problem

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

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

Top Solution Authors