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