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
)

     

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"
    )
  • tamerj1's avatar
    tamerj1
    Community Champion
    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
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    your solution works tamerj1  and will accept it,
    but it creates this short week containing only one day, and I know my colleagues will ask why ğŸ˜…

     

     

    do you maybe know if it's possible to somehow repeat a value from row above on condition from other column,
    like here, I did rankx of Date column

    day_rank =
    RANKX(
        'Calendar',
        'Calendar'[Date],,ASC)
    and maybe it's possible to write a logical function ( using REPT, IF, EARLIER) that will check if next row in Week column is equial to previous one, if true, then repeat Day_rank row, if not then Day_rank?
     

    thanks for all your help!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    tamerj1 , 
    well the last solution actually works fine, thanks again 🙂