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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jhern90
Frequent Visitor

Problem with months not in order on line chart

I have a line chart with data showing number of X vs date. However, I have a problem where two of the months are flipped (see image). I have looked into this community and found similar threads. The solution I read many times was to sort my time by the date column. However that has not worked for me. Someone mentioned changing the x-axis to conitnuous but my chart won't allow me to do that and I don't have a dates table as some mentioned is need for continuous x-axis. Does anyone have any other solutions for this issue?

 

Line chartLine chart

1 ACCEPTED SOLUTION

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:

  1. Click on the Month Name field in the Date table.
  2. Click on the Column Tools menu (this is the new ribbon)
  3. Click Sort By Column
  4. Pick "Month" which is an integer from 1-12 that perfectly corresponds to Jan-Dec.
  5. In your chart, click the elipses and ensure it is being sorted by Month Name, not Sales.

20200219 09_36_25-20200218 - Sort By Column Example - Power BI Desktop.png

Ensuring the chart is sorting correctly.

20200219 09_43_17-20200218 - Sort By Column Example - Power BI Desktop.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

14 REPLIES 14
v-kelly-msft
Community Support
Community Support

Hi @jhern90 ,

 

Go to "edit queries">"Add column">"Index Column ">" from 1":

Annotation 2020-02-12 131115.pngAnnotation 2020-02-12 131225.png

 

Then go back to data view>choose date column>sort by [Index]:

 

Annotation 2020-02-12 131718.png

 

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?

Hi @jhern90 ,

 

What is the data type of your column?Is it a date type?

 

 
Best Regards,
Kelly
 
Did 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.

@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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans, unfortunately I cannot share the file since it contains client data. The month column is a number, I was referring to the original date column. I went back to my data source and update the dates on some rows so I wouldn't have any duplicate rows to see if that would fix it but that didn't seem to fix the problem. 

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:

  1. Click on the Month Name field in the Date table.
  2. Click on the Column Tools menu (this is the new ribbon)
  3. Click Sort By Column
  4. Pick "Month" which is an integer from 1-12 that perfectly corresponds to Jan-Dec.
  5. In your chart, click the elipses and ensure it is being sorted by Month Name, not Sales.

20200219 09_36_25-20200218 - Sort By Column Example - Power BI Desktop.png

Ensuring the chart is sorting correctly.

20200219 09_43_17-20200218 - Sort By Column Example - Power BI Desktop.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Works like a charm. Thank you so much!!!

@edhans, thanks for that. I was able to test your file and was able to play around with it. I don't have a date table which might be onbe of the reasons why I cannot sort my data. I was trying to sort the data based on the date column on the same table. I will try out creating a date table and mapping it to my data table to see if I can make that work. Thanks

Great. You do not have to have a date table, but it makes your life easier if you do, in many ways, and without one, Time Intelligence functions simply do not work.

 

That M code in my file is my standard Date table that I copy and paste in to all new files. I actually use this method to make it 100% dynamic so it is always covering all data in my file.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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. 

 

  1. In Power Query, click on the date column
  2. Click on the Add Column tab in the ribbon
  3. CLick on the dropdown under date, and click on Month, then Month again. Now all January's have 1, Feb have 2, etc.
  4. Sort by that column in the DAX model.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
amitchandak
Super User
Super User

If you only want shown month name then it better than you come by date hierarchy drill. Else preferably have month year and month year sort in your table and use that.

 

Sort_by_column.png

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

edhans
Super User
Super User

You do not have to sort by a date column, but you do have to provide a sortable column in your data set so Power BI knows how to sort. A date table is far and away the best practice here, but you can use any column to sort. See this article.

 

If you cannot get it to work, can you share your PBIX file via OneDrive or other method so someone can take a look at it? There is no workaround here. Sort By Columns is the thing. Period.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@jhern90 on top right corner of your visual, click three dots and make sure your have select correct column for sorting and sorting order is also correct.

 

Would appreciate Kudos 🙂 if my solution helped.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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