Forum Discussion
Sort Week End across Dec 31/Jan 1 in Matrix
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])
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?
