Forum Discussion
Problem with months not in order on line chart
Ok. without something to work with it is difficult to assist. But let me try one more thing.
Download this PBIX file. It has some random data I put together for 2020. Notice how the chart is not sorted by month properly. I'm not sure how it is sorted. It isn't alphapbetical or by date. Do the following:
- Click on the Month Name field in the Date table.
- Click on the Column Tools menu (this is the new ribbon)
- Click Sort By Column
- Pick "Month" which is an integer from 1-12 that perfectly corresponds to Jan-Dec.
- In your chart, click the elipses and ensure it is being sorted by Month Name, not Sales.
Ensuring the chart is sorting correctly.
It should now be sorted by the Month Name, which in turn is sorted by the numerical month number.
See if walking through that process helps. I don't know what else to do if I cannot have access to your PBIX file to more closely examine the issue.
Community SupportHi jhern90 ,
Go to "edit queries">"Add column">"Index Column ">" from 1":
Then go back to data view>choose date column>sort by [Index]:
This would help.
Best Regards,
Kelly
v-kelly-msft , I tried your suggestion but couldn't make it work. When I tried to sort the date column using the index column I got an error saying that I cannot sort it because there are multiple values in the index column for the same value in the date column. My data has multiple rows with the same date. Maybe this is my problem. Unfortunately I cannot get rid of them since they are different records, the other columns have different values, they just happen to have the same date. Do you know of a workaourd for this?
- edhans
Community Champion
You have to have 1 value per sorting value. So if you are sorting by month, January would be 1, February would be 2, etc. By doing the Index, January may have gotten 1, and 13, and 27, etc.
- In Power Query, click on the date column
- Click on the Add Column tab in the ribbon
- CLick on the dropdown under date, and click on Month, then Month again. Now all January's have 1, Feb have 2, etc.
- Sort by that column in the DAX model.
- v-kelly-msft
Hi jhern90 ,
What is the data type of your column?Is it a date type?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!v-kelly-msft , it is a date type. I've tried all the solutions above but none worked. It seems to be the problem that I have multiple dates that are the same and that's causing it to not sort properly, but not sure why that's being a problem.
- edhans
jhern90 the month field in your date table should not be a Date type. A month number is an integer.
The Sort By Column solution works. This isn't a workaround. this is how Power BI and the data model in Excel's Power Pivot are designed. Can you please post some sample data in a PBIX file and share via OneDrive so we can see exactly what your issue is? I am thinking this is a 5 minute fix but we've been guessing what your data model looks like for two weeks. I'd really like to help you move this project forward. If it is confidiential data you don't want to post, but you feel comfortable sharing with me (or someone else in this thread) please share via PM.