Forum Discussion

jaryszek's avatar
jaryszek
Super User
1 year ago
Solved

Change visual sorting based on weeks

hi Guys,

I have visual which should be sorted from Thuesday, not Friday (visual by WeekDays. WeekOfMonth: Week 1, Week 2, Week 3, Week 4-->WeekDay). 


This is the order which i want to show based on week:

 

So it is starting from Tuesday in Week 1, in Week 3 is Wednesday. 

How to sort WeekDay by specific Week and number? 
I tried with this column:


but I am getting :



How to solve it?

In other words:

By changing Weeks in slicer i want to see on Visual sorted WeekDays according to calendar order (from first in week until last):



Best,
Jacek

 

 

 

Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi jaryszek ,

    Please follow below steps.

    1. In Query editor, I have created new column (Data-Copy) based on
    'Date' column in 'Dim_Date' table.

    2. I have done some transformation on (Data-Copy) like change the language and datatype, created custom column with weekday values.

    3. I have created table (table) with Weekday and Sort columns.
    and merged the new table with existing 'Dim_Date' table. and i have expanded the table and removed blank columns.

    4. You can see the 'APPLIED STEPS' in Query editor, it will show each and every steps. Please refer below snap.

     

     

    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

10 Replies

  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    Hi jaryszek ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    You want your visual to show weekdays in correct calendar order for each WeekOfMonth based on actual date sequence, not default weekday names or alphabetically.

     

    Please follow below steps to fix the issue.

     

    1. Created table (Table) with below M code in Query editor.

     

    let
    Source = Table.FromRows({
    {"2025-04-01", "Tuesday", "Week 1"},
    {"2025-04-02", "Wednesday", "Week 1"},
    {"2025-04-03", "Thursday", "Week 1"},
    {"2025-04-04", "Friday", "Week 1"},
    {"2025-04-05", "Saturday", "Week 1"},
    {"2025-04-06", "Sunday", "Week 1"},
    {"2025-04-07", "Monday", "Week 2"},
    {"2025-04-08", "Tuesday", "Week 2"},
    {"2025-04-09", "Wednesday", "Week 2"},
    {"2025-04-10", "Thursday", "Week 2"},
    {"2025-04-11", "Friday", "Week 2"},
    {"2025-04-12", "Saturday", "Week 2"},
    {"2025-04-13", "Sunday", "Week 2"}
    }, {"Date", "WeekDay", "WeekOfMonth"}),

    ChangedTypes = Table.TransformColumnTypes(Source,{
    {"Date", type date}, {"WeekDay", type text}, {"WeekOfMonth", type text}
    })
    in
    ChangedTypes

     

    Please refer snap.

     

     

    2.  Created sorting columns in "Table" View. Please refer snap

     

    SortIndex for sorting by date:  SortIndex = RANKX(ALL('Table'), 'Table'[Date], , ASC)

    Composite key for unique sorting:  WeekDay_Composite = 'Table'[WeekOfMonth] & " - " & 'Table'[WeekDay]

    DisplayWeekDay = 'Table'[WeekDay]

     

     

    3. Sorted Composite column. refer in snap.

    Select WeekDay_Composite column --> Sort by Column --> choose SortIndex.

    Now each WeekDay_Composite ("Week 1 - Tuesday") is sorted by actual date.

     

     

    4. In visual, Drag the field "WeekDay_Composite" in X-axis and measure "Sum of CostInBillingCurrency" in Y-axis.

    Please refer attached PBIX file.

     

     

    If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

    Thank you

     

     

     

     

    • jaryszek's avatar
      jaryszek
      Super User
      1 year ago

      Ok attached example worked.

      I tried similar approach with Weeks:
      Added WekDayKey:

           AddingWeekDayKey = Table.AddColumn(
    #"Added Adjusted_DayOfWeek_Sort",
    "WeekDayKey",
    each [WeekOfMonth] & "-" & Text.From([Day]))


      and sort by SortIndex column. 

      But i do not want to put my WeekDayKey into visual -> only i want to put there weeks like Week1, Week2. 
      The same for WeekDay_Composite

      How to woraround it? 

      Best,
      Jacek




      • v-dineshya's avatar
        v-dineshya
        Community Support
        1 year ago

        Hi jaryszek ,

        You want to sort by a hidden composite key (like WeekDayKey or WeekDay_Composite), but display only WeekDay in the visual and do this dynamically for each selected WeekOfMonth ( Tuesday --> Wednesday for Week 1, Monday --> Tuesday for Week 2, etc).

        Please follow below steps.

        1. Created a composite label column:

        This will be used in the X-axis instead of plain WeekDay:

        DisplayLabel = 'Table'[WeekOfMonth] & "-" & 'Table'[WeekDay]


        2. Created a numeric sort key

        SortIndex = VALUE(FORMAT('Table'[Date], "YYYYMMDD"))

        Note: This gives you a unique number for sorting in true calendar order.

        3. Sort DisplayLabel by SortIndex

        In Data view: Select the DisplayLabel column and Click “Sort by column” --> click SortIndex

        Note: Now your labels are sorted correctly in the visual.

         

        If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

        Thank you