Forum Discussion
Sort Week End across Dec 31/Jan 1 in Matrix
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
5 Replies
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)
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
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
aflintdepm , Sorry, Seem like my mistake try with date a new column
Week Year = "W" & weeknum(Calendar[Date], 2) & "-" & year(Calendar[Date])
