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
Hi,
I have a dataset that is a simple collection of stats per calendar month. The dataset looks like
Period | Month Number | Landing Page Hits | WiFi Sessions | Data Download (GB) |
Mar-18 | 1 | 21556 | 931444 | 1267260 |
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.
Solved! Go to 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.
Are you saying that you can't set your Sort By column to Month Number? Modeling tab Sor By.
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.
Hi Greg,
I am somewhat embarassed to say I never spotted that option but thank you very much it has solved my problem
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.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |