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
Anonymous
Not applicable

Sort Month and Year in Chronological order

Hi Experts,

I am struggling this for some time, I was trying to get an output based on Month & Year. I did try all possible ways to sort that.. But unable to get that in chorological order.

 

MMYYY snapshot.JPGMMYYY snapshot1.JPG

 

My date table looks like this;

 

Date table.JPG

Would be great if someone could assist me with this.,

 

Thanks

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

Create a sort column and mark it as sort column

 

Sort moth year = format(date[date],"YYYYMM")

 

Sort_by_column.png

Appreciate your Kudos.

View solution in original post

Icey
Community Support
Community Support

Hi @Anonymous ,

 

After creating the sort column, did you do this operation?

 

SORT.gif

 

After sorting by YearMonth column, Month & Year column will sort correctly in your visual.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@Anonymous ,

 

I hope you created the column in the same table. And you have to click on the column you want to change sort order. ie the old month year. I have given screenshot where is sort option. In new ribbon, it is under column properties.

Also make sure visual is sorted on month year(the column which you have used in visual)

OLD

Sort_by_column.png

 

NEWSortnewribbion.png

 

Appreciate your Kudos.

View solution in original post

15 REPLIES 15
analytics-ecoma
Frequent Visitor

Hello, 
I have difficulty on the same topic.
I am trying to create a slicer with Day, Week and Monthly view. However, when I try to get the names on the visual, the chronological order is missing. I amu nsure how to proceed from here.
Screenshot 1 : works fine (used "yearmonth number" to sort the column)
Screenshot 2 : chronological order is messed up. 
please help me !

analyticsecoma_0-1676992763976.png

analyticsecoma_1-1676992823513.png

 

Bobbels
Regular Visitor

I realise that this is an old post, but in case anyone stumbles across it, i'll just share how i got it to work.

 

Create a hierarchy, mine looks like this.

Bobbels_0-1662454313974.png

Add to graph, click the three dots in the top right corner and choose sort by what you put in the hierarchy.

 

Now enter your Data tab and find the column in your hierarchy, click it and then click on Sort by column. Now you see a list with options, choose sort by month number. Lookes like this for me 

Bobbels_1-1662454659050.png

Optional for a smother look(in my opinion) go to format visual and make sure that the X-axis values aren't concatenated.

 

Hope it helped.

 

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Is this problem solved?

 

 

Best Regards,

Icey

Anonymous
Not applicable

Hi @Icey ,

 

I am trying to create a column as suggested by @amitchandak . but not gettting an error.

 

 

amitchandak
Super User
Super User

Create a sort column and mark it as sort column

 

Sort moth year = format(date[date],"YYYYMM")

 

Sort_by_column.png

Appreciate your Kudos.

What about when you deal with published datasets , in that case you cannot create a "Calculated" field nor you can create a "Measure" using DAX function as it doesn't recognize the date field in the actual data which in this case is "Invoice Date". When you pull a published data set you dont even see a data model . Thanks

Anonymous
Not applicable

Hi @amitchandak ,

 

Thank you for your reply.

 

I am not getting proper output based on your instructions. Not sure where I am going wrong. I've created a sort column;

 

sort column.JPG

 

I am getting this

 

sort column1.JPG

 

Could u pls suggest?

 

thanks

 

@Anonymous ,

 

I hope you created the column in the same table. And you have to click on the column you want to change sort order. ie the old month year. I have given screenshot where is sort option. In new ribbon, it is under column properties.

Also make sure visual is sorted on month year(the column which you have used in visual)

OLD

Sort_by_column.png

 

NEWSortnewribbion.png

 

Appreciate your Kudos.

Anonymous
Not applicable

All good now. I had to sort in my visuals too.

 

Appreciate your help.

Anonymous
Not applicable

Hi @amitchandak ,

 

Yes, I have created new column (Sort Year Month) in same table(Calendar). Later I'd sort my old Month & Year table by by " Sort Year & Month" column

 

MMYY.JPGYYYMM.JPG

Anonymous
Not applicable

Have you checked the sorting on your graph? You can check it by clicking the 3 dots above your graph. Refer to the picture below.

 

sort.png

Refer to this file. Page 3 has the graph.https://www.dropbox.com/s/d6t8pyhz1vnf1h3/sales_analytics_ringchart.pbix?dl=0

 If possible please share a sample pbix file after removing sensitive information.Thanks.

 

Appreciate your Kudos.

Icey
Community Support
Community Support

Hi @Anonymous ,

 

After creating the sort column, did you do this operation?

 

SORT.gif

 

After sorting by YearMonth column, Month & Year column will sort correctly in your visual.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

this is great! Thank you so much. This solved my problem

Anonymous
Not applicable

Hello,

 

I've done exactly the same as shown in your example. Still the same.

 

visuals.JPG

Do i need to sort my visuals also?

 

sort1.JPG

In "axis" column i've used "Month & Year" column.

 

Is there anything wrong in my model?

 

calendar relations.JPG

 

Thanks

Helpful resources

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

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.