MM-DDDD Sorting Order in Column Visual

[Sudhavi_84]

HI Experts,

I have 4 different calculated Date columns based on criteria, Which I am using for 4 different Column Visuals.

I want to sort Date Columns in to MM-YYYY Order in Column Visual (Horizontal)

I can add 4 more columns for each date column and then  I can give groupings and sort the order.

But with out adding 4 more columns, can I sort the my Horizontal Column Visual in Order?  (Yellow is Blank which has no dates)

I converted Date Column Data Type to MM-YYYY and also tried to Formatted to "MM-YYYY"  but it is not sorting order.

Thanks in Advance

[BA_Pete]

Hi @Sudhavi_84 ,

Create a [yearMonth] field in your calendar.

In Power Query your column calc would be something like this:

Date.Year([Date]) * 100 + Date.Month([Date])

Once you have this column, you can use it as a Sort By column for your MM-YYYY column.

Pete

[Sudhavi_84]

Thank you @BA_Pete  for your quick response.

I have MMMYYYY column in calendar. Which is perfect as below in calendar table.

Next I am going to table-->Adding Column-->Date.Year is greying out not showing me anything. Please let me know if I am missing something here.  

MMYYYY = Date.Year   (I am getting below error)

Please any help?

[Sudhavi_84]

Is this something we need to write in Power Query (M Editor)? Not in new calculated column?

[BA_Pete · Accepted solution]

Hi @Sudhavi_84 ,

Yes, the code I gave was for a new column in Power Query, as DAX calculated columns are not generally recommended.

If you really need to do it in DAX, then the column formula would be:

..yearMonthSortKey = YEAR(yourTable[Date]) * 100 + MONTH(yourTable[Date])

Pete

[Sudhavi_84]

Perfect Thank You