Forum Discussion

Nudo's avatar
Nudo
Helper I
4 years ago
Solved

Sorting a Week Range date column

Hey

I'm trying to create a line graph with weeks as the xaxis and I want to show the week range + year on the xaxis instead of the week num + year. I've created a week range like this 

Week Range = var First = [Date]-WEEKDAY([Date],1)+1
var Last= [Date]-WEEKDAY([Date],1)+7
var concat = FORMAT(First,"d MMM yy") & " - " & FORMAT(Last,"d MMM yy")

return concat

My sort Column looks like this

YearMonthInt = 'Date Table'[Date].[Year]*100 + WEEKNUM('Date Table'[Date], 1)

But I'm getting this error and I can't figure out how to fix this. By my understanding this means theres say 2 values in sort column that match 1 value in Week Range column. I'm not sure what I'm doing wrong though.




Need Help
  • Nudo's avatar
    Nudo
    4 years ago

    Thanks for the reply. I've managed to fix it in a pretty convulted way but it works, not sure why but it does.

    Just incase anyone has this sort of issue in the future what I done is change my week range column to only be the first date(start of week) as date type and then changed the formula in YearMonthInt dates to come from this new column. Then I've created another column with the First date of the week and sorted it by the new YearMonthInt formula, I haven't done it yet but I'm sure I can then Concatenate the end of the week onto this.

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    Nudo 

    that means in your table, one week range value has more than one corresponding values in yearmonthint.

    if you want to sort by another column . There must be one to one relationship between two columns.

     

    • Nudo's avatar
      Nudo
      Helper I
      4 years ago

      Thanks for the reply. I've managed to fix it in a pretty convulted way but it works, not sure why but it does.

      Just incase anyone has this sort of issue in the future what I done is change my week range column to only be the first date(start of week) as date type and then changed the formula in YearMonthInt dates to come from this new column. Then I've created another column with the First date of the week and sorted it by the new YearMonthInt formula, I haven't done it yet but I'm sure I can then Concatenate the end of the week onto this.