Forum Discussion

bslozano's avatar
bslozano
Frequent Visitor
1 year ago
Solved

Sort MonthYear with Unscheduled

I have a MonthYear column in MonthName Year format. I was able to sort that by creating a different column. Now within the MonthYear column, there are some rows that the values are "Unscheduled". How...
Need Help
  • MFelix's avatar
    MFelix
    1 year ago

    Hi bslozano ,

     

    The best option is to have a number for the unschedulled and use a monthyear column so you would get:

    202501 - 2025 January

    202502 - 2025 February

    202503 - 2025 March

    ...

    202513 - 2025 Unschedulled

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi bslozano,

    In your table, add a new calculated column using this DAX:

    SortOrder = 
IF(
    TableName[MonthYear] = "Unscheduled", 
    999999999, 
    YEAR(DATEVALUE("1 " & TableName[MonthYear])) * 100 + MONTH(DATEVALUE("1 " & TableName[MonthYear]))
)

    Replace TableName with your table's name.

    For rows where MonthYear is "Unscheduled", the formula assigns a high value (999999999) to place it at the end.

    For other rows, the formula calculates a numeric value based on the Year and Month (e.g., 202501 for January 2025).

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Hi bslozano,

In your table, add a new calculated column using this DAX:

SortOrder = 
IF(
    TableName[MonthYear] = "Unscheduled", 
    999999999, 
    YEAR(DATEVALUE("1 " & TableName[MonthYear])) * 100 + MONTH(DATEVALUE("1 " & TableName[MonthYear]))
)

Replace TableName with your table's name.

For rows where MonthYear is "Unscheduled", the formula assigns a high value (999999999) to place it at the end.

For other rows, the formula calculates a numeric value based on the Year and Month (e.g., 202501 for January 2025).