Forum Discussion

aflintdepm's avatar
aflintdepm
Helper III
3 years ago

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 w...
Need Help
Show and Tell
amitchandak's avatar
amitchandak
Super User
3 years ago

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's avatar
aflintdepm
Helper III
3 years ago

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