Forum Discussion

Helper V

5 years ago

Solved

MM-DDDD Sorting Order in Column Visual

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
5 years ago
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

5 Replies

BA_Pete
Super User
5 years ago
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
  Helper V
  5 years ago
  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
    Helper V
    5 years ago
    Is this something we need to write in Power Query (M Editor)? Not in new calculated column?