Sort week duration

[ashaikh]

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.

[Greg_Deckler]

I created the Sequential Quick Measure for exactly this purpose. https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231

[ashaikh]

@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 · Accepted solution]

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

[Greg_Deckler]

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?