Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rank date column

Hi,


Can someone please help writing DAX RANK() / RANKX() using Year and Weeknumber column. Last column "WeekNumber" is the desired column through that function.

 

TimeIDDateYearMonthMonthNameWeekIsWorkingDayWeekRangeWeeNumber
2020030101 March 202020203March10001 Mar 20 - 07 Mar 20Week 1
2020030202 March 202020203March10101 Mar 20 - 07 Mar 20Week 1
2020030303 March 202020203March10101 Mar 20 - 07 Mar 20Week 1
2020030404 March 202020203March10101 Mar 20 - 07 Mar 20Week 1
2020030505 March 202020203March10101 Mar 20 - 07 Mar 20Week 1
2020030606 March 202020203March10101 Mar 20 - 07 Mar 20Week 1
2020030707 March 202020203March10001 Mar 20 - 07 Mar 20Week 1
2020030808 March 202020203March11008 Mar 20 - 14 Mar 20Week 2
2020030909 March 202020203March11108 Mar 20 - 14 Mar 20Week 2
2020031010 March 202020203March11108 Mar 20 - 14 Mar 20Week 2
2020031111 March 202020203March11108 Mar 20 - 14 Mar 20Week 2
2020031212 March 202020203March11108 Mar 20 - 14 Mar 20Week 2
2020031313 March 202020203March11108 Mar 20 - 14 Mar 20Week 2
2020031414 March 202020203March11008 Mar 20 - 14 Mar 20Week 2
2020031515 March 202020203March12015 Mar 20 - 21 Mar 20Week 3
2020031616 March 202020203March12115 Mar 20 - 21 Mar 20Week 3
2020031717 March 202020203March12115 Mar 20 - 21 Mar 20Week 3
2020031818 March 202020203March12115 Mar 20 - 21 Mar 20Week 3
2020031919 March 202020203March12115 Mar 20 - 21 Mar 20Week 3
2020032020 March 202020203March12115 Mar 20 - 21 Mar 20Week 3
2020032121 March 202020203March12015 Mar 20 - 21 Mar 20Week 3

 

Many thanks

Help Requested
  • mahoneypat's avatar
    mahoneypat
    6 years ago
    Please try this expression in your column.  It calculates the weeks difference within the same year from the minimum date in that year (and concatenates it with "Week ").
     
    WeekNumber = var mindatesameyear = CALCULATE(MIN('Date'[Date]), all('Date'), 'Date'[Year]=EARLIER('Date'[Year]))
    var weeksfrommin = ROUNDDOWN(DATEDIFF(mindatesameyear, 'Date'[Date],DAY)/7,0)+1
    return "Week " & weeksfrommin
     
    If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.
    Regards,
    Pat

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

    I have removed unnecessry columns and wanted to calculate last column through DAX RANK using Year and Week column. 

     

    DateYearMonthWeekWeeNumber
    01 March 20202020310Week 1
    02 March 20202020310Week 1
    03 March 20202020310Week 1
    04 March 20202020310Week 1
    05 March 20202020310Week 1
    06 March 20202020310Week 1
    07 March 20202020310Week 1
    08 March 20202020311Week 2
    09 March 20202020311Week 2
    10 March 20202020311Week 2
    11 March 20202020311Week 2
    12 March 20202020311Week 2
    13 March 20202020311Week 2
    14 March 20202020311Week 2
    15 March 20202020312Week 3
    16 March 20202020312Week 3
    17 March 20202020312Week 3
    18 March 20202020312Week 3
    19 March 20202020312Week 3
    20 March 20202020312Week 3
    21 March 20202020312Week 3
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      Create a Calculated Column

       

      Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date])+1
       
      Create another Calculated Column,
       
      Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
       
      Regards,
      Harsh Nathani
       
      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks Anonymous 

         

        It partially works. 

        I have date table which contains dates starting from year 2015 till 2040. I just copied a three weeks data.

        Reason I wanted to Rank by [Year] and [Week] because every year ranking should start from 1. 

         

        Is it possible to copy data into PBIX file and attach back here. I wish I could attach a full file. 

         

        Many thanks