Forum Discussion

maziiw's avatar
maziiw
Advocate I
1 year ago
Solved

Sort by Year Desc and Week Desc

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

 

 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    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:

     

4 Replies

  • maruthisp's avatar
    maruthisp
    Super User
    1 year ago

    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

  • sevenhills's avatar
    sevenhills
    Super User
    1 year ago

     

    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.

       

       

       

       



  • danextian's avatar
    danextian
    Super User
    1 year ago

    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: