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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.