The graphs represents Oct, Nov and Dec of 2020. And Jan 2021. How do I make Power BI sort by month and year? In the [Data] section, I told Power BI to sort by date, but the visualization is not updating. How do I fix this?
Asked
Active
Viewed 3,918 times
0
-
show the sort settings for the visual and show the field well for the X axis. Is it really a time axis? Or is it a category axis with months as text labels, sorted from small to large? – teylyn Feb 01 '21 at 20:26
-
Show the sort order of the visual. – teylyn Feb 01 '21 at 20:29
-
Where do I find that? – user716255 Feb 01 '21 at 20:30
2 Answers
2
It looks like you have removed parts of the date hierarchy. That makes the axis a categorical axis instead of a timeline.
This results in the wrong sort order for the months. It is now sorting descending by month number, 12, 11, 10 and 1.
You may want to look into using a calendar table or adding a few sort helper columns to your source table.
The screenshot shows a similar scenario:
I add a sort column to the data source and use the Sort By command in the data model to sort the Month Name column by the Date column.
After that, the visual shows in the correct order.
Edit: Another approach can work if you only have one date for each month, so that will not work if you have many dates contributing to each of the monthly bars. My data only has one date per month and category, so I can just use the "Date" in the axis instead of the "Date Hierarchy". This retains the time axis properties and does not turn the axis into a categorical axis.
teylyn
- 34,374
- 4
- 53
- 73
-
If that resolved your issue, please mark the answer as described in the [tour]. If it didn't, please leave a comment, so I can follow up. – teylyn Feb 01 '21 at 20:37
-
I tried the last approach you suggested. The axis shifted the columns titles to the left. Instead of show Oct 2020, Nov 2020, Dec 2020 & Jan 2021. The visualization shows, Nov 2020, Dec 2020, Jan 2021 and the last graph is blank. – user716255 Feb 01 '21 at 21:25
-
This is hard to troubleshoot if you only describe what you see. Edit your question and add screenshots showing the settings, as you can see in my screenshots. – teylyn Feb 01 '21 at 21:59
-
I posted a new question. implemented your suggestions. This created a new ask. Thanks for your aid. https://stackoverflow.com/questions/66000724/power-bi-visualization-result-shifting-left – user716255 Feb 01 '21 at 22:09
-
Why create another question? Is this one solved? Then mark it. If it isn't solved, then continue here. – teylyn Feb 02 '21 at 02:17
0
For year month, you can make a calculated column and magnify year's importance by multiply to 100 and add month. And then sort your column by new new column. I'd rather put year month :)
Here is a comprehensive video about calendar in power BI also :
Theo
- 21
- 3