Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
I created the Sequential Quick Measure for exactly this purpose. https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231
@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.
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |