cancel
Showing results for
Did you mean:
Post Partisan

## Year Sort

I've made a calculated column for Month and year and It's showing as below:

The sorting is not correct it seems. I want the Month and Year to be sorted.

Desired Output

 Deal Month Year xyz Jan'2025 xyz Feb'2025 xyz Jan'2026 xyz Feb'2026

How can I get the sorting right? Present I've sorted month year by Month.

Below are the formulas I've used:

Month Year = VIEW_FORECASTREVENUE[Month Name] & "'" & VIEW_FORECASTREVENUE[Year]

Month Name = SWITCH(VIEW_FORECASTREVENUE[Month],1,"Jan",2
,"Feb",3,"Mar",4,"April",5,"May",6,"June",7
,"July",8,"Aug",9,"Sep",10,"Oct"
,11,"Nov",12,"Dec")

Year = YEAR(VIEW_FORECASTREVENUE[EXPECTEDINMONTH])

Regards,
Himanshu
2 ACCEPTED SOLUTIONS
Super User

Create your column as actual dates and then they will sort properly.

Try this to create the column

``Month Year = DATE(YEAR(VIEW_FORECASTREVENUE[EXPECTEDINMONTH]), VIEW_FORECASTREVENUE[Month], 1)``

You should get a column of type DateTime that should sort chronologically

Regards

Phil

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Blog :: YouTube Channel :: Connect on Linkedin

Proud to be a Super User!

Super User

Hi @Himanshu_13
You can create a numeric key like 202101 for January 2021 and then use this column for sorting the Month-Year column using the sort by column option.

7 REPLIES 7
Community Champion

HI @Himanshu_13 ,

See if this helps

https://community.powerbi.com/t5/Desktop/how-to-sort-Date-MonthYear-column/td-p/102700

https://community.powerbi.com/t5/Desktop/Sorting-of-Month-Year/td-p/500324

Basically you need to create a Index Column and then sort your Month Year Column via the index colum created.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Super User

Hi @Himanshu_13
You can create a numeric key like 202101 for January 2021 and then use this column for sorting the Month-Year column using the sort by column option.

Post Partisan

Thanks it solved my issue.

Regards,

Himanshu

Super User

Create your column as actual dates and then they will sort properly.

Try this to create the column

``Month Year = DATE(YEAR(VIEW_FORECASTREVENUE[EXPECTEDINMONTH]), VIEW_FORECASTREVENUE[Month], 1)``

You should get a column of type DateTime that should sort chronologically

Regards

Phil

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Blog :: YouTube Channel :: Connect on Linkedin

Proud to be a Super User!

Post Partisan

Thank you !!!

It resolved my issue.

Regards,

Himanshu

Post Partisan

Can you please show what actually you mean by "Actual dates". It is not clear to me.

Regards

Super User

Just added an example of how to create the dates based on your data.

``Month Year = DATE(YEAR(VIEW_FORECASTREVENUE[EXPECTEDINMONTH]), VIEW_FORECASTREVENUE[Month], 1)``

Regards

Phil

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Blog :: YouTube Channel :: Connect on Linkedin

Proud to be a Super User!

Announcements