Sort by Year Desc and Week Desc

[maziiw]

Could someone help with how to sort the following visual, so that I see 2025 first then 2024 and week numbers within each also descending?

I try to "sort by column"" but getting error "There cannot be more than one value in YearWeekSort for the same value in Week of Year

[danextian · Accepted solution]

hi @maziiw 

The slicer currently supports sorting by a single column only so it is either year or week number. I would create a reversed week number column in M. You could create one in DAX by using RANKX on the week number but that would lead the a cyclic reference error as the RANKX column is referencing the week column and the latter is being sorted by the former. Here's a sample M calendar

// Query1
let
    StartDate = #date(2024, 1, 1),
    EndDate = #date(2024, 12, 31),
    DayCount = Duration.Days(EndDate - StartDate) + 1,
    Dates = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    CalendarTable = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),

    // Add calendar components
    AddYear = Table.AddColumn(CalendarTable, "Year", each Date.Year([Date]), Int64.Type),
    AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date]), Int64.Type),
    AddDay = Table.AddColumn(AddMonth, "Day", each Date.Day([Date]), Int64.Type),
    AddWeek = Table.AddColumn(AddDay, "Week Number", each Date.WeekOfYear([Date], Day.Monday), Int64.Type),

    // Get total number of weeks in the year
    LastDateOfYear = #date(2024, 12, 31),
    LastWeekNumber = Date.WeekOfYear(LastDateOfYear, Day.Monday),

    // Add Reverse Week Number
    AddReverseWeek = Table.AddColumn(AddWeek, "Reverse Week Number", each LastWeekNumber - [Week Number] + 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(AddReverseWeek,{{"Date", type date}})
in
    #"Changed Type"

No need to create a custom sort column for the year. Just sort in descending order. Note:

[sevenhills]

Alternate Display of Weeks as Year Weeks in Your Slicer

1. Year Week Sort You may already have this, but if you'd like to modify yours, use:

   DAX

   YearWeekSort = Year('DateTable'[Date]) & "-" & Right( "0" & WEEKNUM('DateTable'[Date]), 2)

2. Create a Hidden Column Add a column and mark it as "Hide in report view":

   DAX

   Year Week Rank = 5000 - ('Date disconnected'[Year] + WEEKNUM('Date disconnected'[Date]))

3. Sorting Use YearWeekRank to sort YearWeekSort.
   

4. Apply Sorting in the Slicer Set the Year sorting order to Descending.

   

5. sample output:

   

   Alternative Sorting Option for Weeks in Your Slicer

   Note: If you want the descending order to apply only to the week number and not the year, follow these steps:

   ~ create a column:
   WeekNumberSort = 100 - WEEKNUM([Date])
   Mark it as "Hide in report view".
   
   ~ Use WeekNumberSort as the sort column for the Week Number field.

   

   
   ~ In the slicer, add Year and Week Number, then set the Year sorting order to Descending.
   
   

   This will ensure that the weeks are sorted properly while maintaining the correct year order.
   

   

[maruthisp]

Hi @maziiw ,

If I understand properly, as I couldn't see the images in the original post. 

To sort DATE columns:
Year column
YearSort column - INT type values
Use Year column to sort by YearSort column

To sort DATE columns:
Week column
WeekSort column - INT type values
Use Week column to sort by WeekSort column

OR

Usually, I use SHIFT key on the keyboard to sort multiple columns on a table visual.

1. Create a table visual:

   • Add "Year" and "Week" to the table visual.
   • Click on the column headers in the visual to sort:
     • First click on "Year" to sort by year.
     • Then hold Shift and click on "Week" to add it as a secondary sort.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[ryan_mayu]

@maziiw 

here is a workaround for you, created the date table in PQ, then you can sort the week column by custome column

pls see the attachment below