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

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's avatar
    amitchandak
    Super User
    3 years ago

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

     

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

    • aflintdepm's avatar
      aflintdepm
      Helper III
      3 years ago

      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?