Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Week sort

Hi all!   please help me with the following,   I have a column Week , which contains begging and end of week, I was planning to sort it by date, but power bi returns an error "Sort by another co...
DAX
Need Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    You are digging in the wrong location. When to stop repeating? That brings you back to the original problem of ranking using the correct granularity. This is an infinite loop and will lead to nowhere.

    The issue is that your business logic wasn't clarified so I had to guess. You can achieve this using

    day_rank =
RANKX (
    'Calendar',
    'Calendar'[Date] - WEEKDAY ( 'Calendar'[Date], 2 ),
    ASC,
    DENSE
)

     

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Anonymous 
Aha. Then please try

day_rank =
RANKX (
    'Calendar',
    VALUE ( SUBSTITUTE ( 'Calendar'[Week_Sort], "_", "" ) ),
    ASC,
    Dense
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

tamerj1 ,

 

same problem,

one value from Week column links to two values from Day_rank column 

 

 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Anonymous 
    These are in two different years. The week sort says one is the last week of 2018 and the other is the first week 2019.  You shuld fix the week sort column, either to consider these 7 days as the last week of 2018 or to consider then as the first week of 2019. I mean week and week sort must be alligned.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      tamerj1 ,

       

      I see, but how? 🙂

       

      Formula in Week column is:

      Week =
      FORMAT(
          'Calendar'[Date] - WEEKDAY('Calendar'[Date],2)+1,
          "dd-MMM")
          &" - "&
      FORMAT(
          'Calendar'[Date] - WEEKDAY('Calendar'[Date],2)+7,
          "dd-MMM")
      and it's automatically catches those days, it there a way to limit which days fall into which week?

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Anonymous 
        Please try

        Week =
FORMAT ( 'Calendar'[Date] - WEEKDAY ( 'Calendar'[Date], 2 ) + 1, "dd-MMM" ) & " - "
    & FORMAT (
        MIN (
            'Calendar'[Date] - WEEKDAY ( 'Calendar'[Date], 2 ) + 7,
            ENDOFYEAR ( 'Calendar'[Date] )
        ),
        "dd-MMM"
    )