Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
jaryszek
Post Prodigy
Post Prodigy

Change visual sorting based on weeks

Monday

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). 

jaryszek_0-1750663402736.png

jaryszek_2-1750663633601.png


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

jaryszek_1-1750663431077.png

 

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:

jaryszek_3-1750663679059.png


but I am getting :

jaryszek_4-1750663697144.png



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):

jaryszek_0-1750664042965.png



Best,
Jacek

 

 

 

Labels:
Message 1 of 8
220 Views
0
7 REPLIES 7
v-dineshya
Community Support
Community Support

Monday

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.

 

vdineshya_0-1750671628546.png

 

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]

 

vdineshya_1-1750672035548.png

 

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.

 

vdineshya_2-1750672258834.png

 

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

 

 

 

 

weeks.pbix
Message 2 of 8
178 Views
0
jaryszek
Post Prodigy
Post Prodigy
In response to v-dineshya

Monday

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




Message 3 of 8
166 Views
0
v-dineshya
Community Support
Community Support
In response to jaryszek

Monday

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

 

Message 4 of 8
100 Views
0
jaryszek
Post Prodigy
Post Prodigy
In response to v-dineshya

yesterday

Ok i have managed to change labels using dax like :

DisplayLabelNoRepeats = 
VAR CurrentLabel = [DisplayLabel]
VAR CurrentDate = [DateKey]
VAR LabelCount =
    CALCULATE(
        COUNTROWS('Dim_Date'),
        FILTER(
            'Dim_Date',
            [DisplayLabel] = CurrentLabel &&
            [DateKey] <= CurrentDate
        )
    )
RETURN
    IF(LabelCount = 1,
        CurrentLabel,
        CurrentLabel & " " & LabelCount
    )


and got DisplayLabelNoRepeats column sortd properly. 

But why this is shown like here? 

jaryszek_0-1750753066033.png
I want to only display WeekDay, no created label...
How to hide the column ? (tried Hide in report view but it didnt work). 

Best,
Jacek

 

Message 6 of 8
66 Views
0
v-dineshya
Community Support
Community Support
In response to jaryszek

yesterday

Hi @jaryszek ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.


Thank you

Message 7 of 8
54 Views
0
jaryszek
Post Prodigy
Post Prodigy
In response to v-dineshya

yesterday

I provided data, my dim_table @v-dineshya 

You can add another table like Date, Cost and that is all. 

How can i upload power bi sample here? 

Edit:
just copy my Fct_Cost table from here:
Date           KeyCost

20250401135.27
20250402142.58
20250403132.15
20250404165.42
20250405178.96
20250406119.80
20250407153.77
20250408144.20
20250409123.54
20250410151.62
20250411138.79
20250412169.40
20250413110.95
20250414159.68
20250415136.84
20250416126.21
20250417158.93
20250418174.32
20250419162.08
20250420118.52
20250421148.16
20250422124.75
20250423139.67
20250424150.44
20250425172.51
20250426180.17
20250427116.38
20250428141.90
20250429130.63
20250430155.09



ModelView:

jaryszek_0-1750761274126.png
I provided 2 tables in order to reproduce the case. 

jaryszek_1-1750761621782.png


you see the filtering? Week1 should start from Thuesday, not from friday...
Plus i do not want to display labels but only Weekdays...

Best,
Jacek

 

 





Message 8 of 8
42 Views
0
jaryszek
Post Prodigy
Post Prodigy
In response to v-dineshya

yesterday

Thanks is still not working:

jaryszek_0-1750750094279.png

 



Let me copy my Dim_Table here. 
DateDateKeyDayOfYearFiscalQuarterFiscalYearIsHolidayIsWeekendMonthMonthNoMonthShortQueryNoWeekDayWeekNoYearYearMonthYearQuarterDayWeekOfMonthWeekDayNumberWeekOfMonth_SortMinDateInWeekWeekDay_SortDisplayLabelSortIndex

wtorek, 1 kwietnia 2025202504019122025FalseFalseApril4Apr4Tuesday1420252025-0421Week 121wtorek, 1 kwietnia 202511Week 1-Tuesday20250401
środa, 2 kwietnia 2025202504029222025FalseFalseApril4Apr4Wednesday1420252025-0422Week 131wtorek, 1 kwietnia 202512Week 1-Wednesday20250402
czwartek, 3 kwietnia 2025202504039322025FalseFalseApril4Apr4Thursday1420252025-0423Week 141wtorek, 1 kwietnia 202513Week 1-Thursday20250403
piątek, 4 kwietnia 2025202504049422025FalseTrueApril4Apr4Friday1420252025-0424Week 151wtorek, 1 kwietnia 202514Week 1-Friday20250404
sobota, 5 kwietnia 2025202504059522025FalseTrueApril4Apr4Saturday1420252025-0425Week 161wtorek, 1 kwietnia 202515Week 1-Saturday20250405
niedziela, 6 kwietnia 2025202504069622025FalseFalseApril4Apr4Sunday1420252025-0426Week 171wtorek, 1 kwietnia 202516Week 1-Sunday20250406
poniedziałek, 7 kwietnia 2025202504079722025FalseFalseApril4Apr4Monday1520252025-0427Week 111wtorek, 1 kwietnia 202517Week 1-Monday20250407
wtorek, 8 kwietnia 2025202504089822025FalseFalseApril4Apr4Tuesday1520252025-0428Week 222wtorek, 8 kwietnia 202521Week 2-Tuesday20250408
środa, 9 kwietnia 2025202504099922025FalseFalseApril4Apr4Wednesday1520252025-0429Week 232wtorek, 8 kwietnia 202522Week 2-Wednesday20250409
czwartek, 10 kwietnia 20252025041010022025FalseFalseApril4Apr4Thursday1520252025-04210Week 242wtorek, 8 kwietnia 202523Week 2-Thursday20250410
piątek, 11 kwietnia 20252025041110122025FalseTrueApril4Apr4Friday1520252025-04211Week 252wtorek, 8 kwietnia 202524Week 2-Friday20250411
sobota, 12 kwietnia 20252025041210222025FalseTrueApril4Apr4Saturday1520252025-04212Week 262wtorek, 8 kwietnia 202525Week 2-Saturday20250412
niedziela, 13 kwietnia 20252025041310322025FalseFalseApril4Apr4Sunday1520252025-04213Week 272wtorek, 8 kwietnia 202526Week 2-Sunday20250413
poniedziałek, 14 kwietnia 20252025041410422025FalseFalseApril4Apr4Monday1620252025-04214Week 212wtorek, 8 kwietnia 202527Week 2-Monday20250414
wtorek, 15 kwietnia 20252025041510522025FalseFalseApril4Apr4Tuesday1620252025-04215Week 222wtorek, 8 kwietnia 202521Week 2-Tuesday20250415
środa, 16 kwietnia 20252025041610622025FalseFalseApril4Apr4Wednesday1620252025-04216Week 333środa, 16 kwietnia 202531Week 3-Wednesday20250416
czwartek, 17 kwietnia 20252025041710722025FalseFalseApril4Apr4Thursday1620252025-04217Week 343środa, 16 kwietnia 202532Week 3-Thursday20250417
piątek, 18 kwietnia 20252025041810822025FalseTrueApril4Apr4Friday1620252025-04218Week 353środa, 16 kwietnia 202533Week 3-Friday20250418
sobota, 19 kwietnia 20252025041910922025FalseTrueApril4Apr4Saturday1620252025-04219Week 363środa, 16 kwietnia 202534Week 3-Saturday20250419
niedziela, 20 kwietnia 20252025042011022025FalseFalseApril4Apr4Sunday1620252025-04220Week 373środa, 16 kwietnia 202535Week 3-Sunday20250420
poniedziałek, 21 kwietnia 20252025042111122025FalseFalseApril4Apr4Monday1720252025-04221Week 313środa, 16 kwietnia 202536Week 3-Monday20250421
wtorek, 22 kwietnia 20252025042211222025FalseFalseApril4Apr4Tuesday1720252025-04222Week 323środa, 16 kwietnia 202537Week 3-Tuesday20250422
środa, 23 kwietnia 20252025042311322025FalseFalseApril4Apr4Wednesday1720252025-04223Week 434środa, 23 kwietnia 202541Week 4-Wednesday20250423
czwartek, 24 kwietnia 20252025042411422025FalseFalseApril4Apr4Thursday1720252025-04224Week 444środa, 23 kwietnia 202542Week 4-Thursday20250424
piątek, 25 kwietnia 20252025042511522025FalseTrueApril4Apr4Friday1720252025-04225Week 454środa, 23 kwietnia 202543Week 4-Friday20250425
sobota, 26 kwietnia 20252025042611622025FalseTrueApril4Apr4Saturday1720252025-04226Week 464środa, 23 kwietnia 202544Week 4-Saturday20250426
niedziela, 27 kwietnia 20252025042711722025FalseFalseApril4Apr4Sunday1720252025-04227Week 474środa, 23 kwietnia 202545Week 4-Sunday20250427
poniedziałek, 28 kwietnia 20252025042811822025FalseFalseApril4Apr4Monday1820252025-04228Week 414środa, 23 kwietnia 202546Week 4-Monday20250428
wtorek, 29 kwietnia 20252025042911922025FalseFalseApril4Apr4Tuesday1820252025-04229Week 424środa, 23 kwietnia 202547Week 4-Tuesday20250429
środa, 30 kwietnia 20252025043012022025FalseFalseApril4Apr4Wednesday1820252025-04230Week 434środa, 23 kwietnia 202541Week 4-Wednesday20250430

 

Put this table to chat gpt, write prompt: "make for me a formatted table from it to copy to excel" and you will get my Dim_date table. 

Why I am getting this error?

And second question is: How to hidden displayKey on the visual? 

 

Best,
Jacek

Message 5 of 8
76 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

View All