Sort Week End across Dec 31/Jan 1 in Matrix

[aflintdepm]

I'm trying to create a matrix with the following structure:

Rows: Business Unit

Columns: End of Week (Mon-Sun)

Values: % Tasks Completed

Filter: Last 12 Weeks (relative)

Because the last 12 weeks crosses back into 2022, my columns are out of order, putting the "earlier" January weeks from 2023 before the "later" Nov/Dec weeks from 2022

I am using a dedicated date table where I have created the necessary columns, as well as the often-recommended 'year-week' column for sorting.  However, no matter what column I try to sort by, I get some version of this error message:

where "CHC End of Week" is my company's calendar and "Sort Order" is a column created by

Sort Order = Calendar[Year] & Calendar[Week of Year]

I understand that the issue will resolve itself once I get past the turn of the year, but I need this tool to work each year going forward.

Thank for any help you can provide

[amitchandak]

@aflintdepm ,

Make sure both are based on monday

example

Week Year = "W" & weeknum(Calendar[Year], 2) & "-" year(Calendar[Year])

Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

[aflintdepm]

@amitchandak 

I have attempted to add duplicate columns based on your formulas.  When I do, i receive a syntax error

When I add in an extra ampersand I get this

For the second formular, I also get a similar error

Not sure what I'm doing wrong, but any advice is appreicated

[amitchandak]

@aflintdepm , Sorry, Seem like my mistake try with date a new column

Week Year = "W" & weeknum(Calendar[Date], 2) & "-"  & year(Calendar[Date])

[aflintdepm]

@amitchandak  Thank you for your continued help.  Your formula worked as expected, however, it did not result in the correct sorting order in the matrix.

Also, I can't use this column to sort my "End of Week" column because I get this error:

Any idea what I'm doing incorrectly?

[aflintdepm]

Please bear with me on this, but I don't understand the instructions you provided.

When I created the calendar table, I created the columns in Power Query using Add Column -> Date, then selected the type of column.  This is the formula that generates my company end of week:

Date.EndOfWeek([Date],Day.Monday)

This is the formula that generates my Week Number

Date.WeekOfYear([CHC End of Week])

I'm not sure how I get your formula into my calendar table.  If I have to add additional columns, I can do that