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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Datagulf
Responsive Resident
Responsive Resident

Sort a Month Year Column

I am trying to sort a column called MOnth Year Num. How can I create a column to sort it?

Datagulf_0-1674558350236.png


I have a Bar chart that shows the last 3 months. So I want to sort that column so that October 2022 comes fast. 

Datagulf_0-1674559644606.png

 

 

 

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Hi,

One of ways to create a sort column for the Month Year Num column is to create a ENDOFMONTH column by using a DATE column and EOMONTH DAX formula.

 

EOMONTH function (DAX) - DAX | Microsoft Learn

 

I hope this helps.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

Anonymous
Not applicable

Month and Year = FORMAT([Date], "YYYY") & MONTH('Calendar'[Date])
 
try this, let me know if it works 🙂 

View solution in original post

11 REPLIES 11
LP280388
Resolver II
Resolver II

@Datagulf,
you can create a Date_start column as below. 

LP280388_0-1674563143857.png

then create a copy of the "month year num" column and use the Date_start column to sort.  We create the copy of the column to ensure that you wont get the circular dependancy error.

LP280388_1-1674563227353.png

then use the newly created copy of the column in your chart as below.

LP280388_2-1674563317629.png

 

 

Anonymous
Not applicable

Can we get more info on how you're trying to sort it? I can see you've made a sort column.

ADPowerBI1_0-1674558997757.png

 

Can you select the sort column from here?

Thanks, I am looking for sth different, which is to sort a  Month Year Num. Your Solutions is for a fiscal year. 
I have a Bar chart that shows the last 3 months. So I want to sort that column so that October 2022 comes fast

Datagulf_0-1674559499266.png

 



Anonymous
Not applicable

Create a Month number + Year column

Year('Date') & Month(date).

 

Put this column in the Tooltip part of your visual

ADPowerBI1_0-1674559741522.png

Then in the top right of your visual, sort by that column

ADPowerBI1_1-1674559775673.png

Hope this helps!

 

It has ordered but not correctly...
2023 Jan comes at the right position but  2022 months are not ordered...

Datagulf_0-1674561434667.png

 

Anonymous
Not applicable

Can you show me your calculated Column code for the sort please? 🙂 

Month and Year = FORMAT([Date], "YYYY") & FORMAT('Calendar'[Date],"mmmm")
Anonymous
Not applicable

Month and Year = FORMAT([Date], "YYYY") & MONTH('Calendar'[Date])
 
try this, let me know if it works 🙂 
Anonymous
Not applicable

or try YEAR('Calendar'[Date] & MONTH('Calendar'[Date])

 

this is how i would write it 🙂 

Jihwan_Kim
Super User
Super User

Hi,

One of ways to create a sort column for the Month Year Num column is to create a ENDOFMONTH column by using a DATE column and EOMONTH DAX formula.

 

EOMONTH function (DAX) - DAX | Microsoft Learn

 

I hope this helps.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Please expound, I did not understand your solution..

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.