cancel
Showing results for
Did you mean:
Helper I

## Return weeks as 1,2,3 from weeks in middle of a year

Hi

I have produced a data set by week and it returns (in tis example, but varies) 47,48 & 79

looking to create a measure to return the first week from a date

I created a column to return the Week Number

Week = WEEKNUM([TimeStart]) and it returns 47,48, 49 etc

Now I want it to show 1,2,3 etc so I looking at something like

Week = WEEKNUM([TimeStart]) -Measure to calculate first week +1

Or any better way.

1 ACCEPTED SOLUTION
Community Support

Hi @flemingg62 ,

Based on your description, I have created a simple sample:

If you want  a calculated column:

``Column = RANKX('Table',WEEKNUM([TimeStart]),,ASC,Dense)``

Output:

If you need a measure:

``Measure = RANKX(ALL('Table'),WEEKNUM([TimeStart]),WEEKNUM(MAX('Table'[TimeStart])),ASC,Dense)``

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi @flemingg62 ,

Based on your description, I have created a simple sample:

If you want  a calculated column:

``Column = RANKX('Table',WEEKNUM([TimeStart]),,ASC,Dense)``

Output:

If you need a measure:

``Measure = RANKX(ALL('Table'),WEEKNUM([TimeStart]),WEEKNUM(MAX('Table'[TimeStart])),ASC,Dense)``

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

Not quite, but  it help me solve it , Thanks

Week = WEEKNUM([TimeStart])-WEEKNUM(MIN(BI_Slots[TimeStart]))+1
Super User

@flemingg62 , refer if these columns can help

Start Month = eomonth([Date],-1)+0
Start of Week = [Date] -WEEKDAY([Date],2)+1 //monday
Month Start week = [Start Month] -WEEKDAY([Start Month ],2)+1 //monday
Month Week = QUOTIENT(DATEDIFF([Month Start week],[Date],DAY),7)+1

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!