Forum Discussion

ashaikh's avatar
ashaikh
Helper III
7 years ago
Solved

Sort week duration

Hello,

 

I have a week duration column which is StartDate of the week and enddate of the week.

 

    WeekDuration
   12/16/18 - 12/22/18
   12/23/18 - 12/29/18
   12/30/18 - 01/05/19
   01/06/19 - 01/12/19

Now If I sort this WeekDuration by WeekNumber using weeknum function this is what I get

 

    WeekDuration        WeekNumber
   12/16/18 - 12/22/18  51
   12/23/18 - 12/29/18  52
   12/30/18 - 01/05/19  53
   12/30/18 - 01/05/19  1
   01/06/19 - 01/12/19  2

Now as you see Week of 12/30/18 - 01/05/19 comes twice and this way it cannot be sorted.

 

I need to sort in a way where in weeknumber after end of December would be incremented by 1 instead of dupliating WeekDuration. 

 

Expected Result

    WeekDuration        WeekNumber
   12/16/18 - 12/22/18  51
   12/23/18 - 12/29/18  52
   12/30/18 - 01/05/19  53
   01/06/19 - 01/12/19  54

Is there any where to do this.

 

Any help is appreciated.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    Here is what I did and I get a sequential number:

     

    Sequential = 
VAR MaxWeeks = SUMMARIZE(ALL('YearWeeks'),'YearWeeks'[Year],"MaxWeek",MAX('YearWeeks'[WeekNumber]))
VAR MyYear = [Year]
VAR MyStart = SUMX(FILTER(MaxWeeks,[Year]<MyYear),[MaxWeek])
VAR firstYear = CALCULATE(FIRSTNONBLANK('YearWeeks'[Year],1),ALL('YearWeeks'))
VAR myNum = IF(MyYear=firstYear,[WeekNumber],MyStart+[WeekNumber])
RETURN myNum

    Attached

4 Replies

    • ashaikh's avatar
      ashaikh
      Helper III
      7 years ago

      Greg_Deckler Thanks for the reply I tried the solution but its not working for me in this case. I am still getting duplicates when I add week duration. The overlaping week between 2018 and 2019 is still coming twice.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        7 years ago

        Well, that's because the week is listed twice. Let me think about how to break that out. I think what you really need is a separate table of years and weeknums. I'll have to model out what you are doing. What does your implementation look like in terms of the formulas you used?