New column where it gives same output for each 7 rows

[raassd]

Hi Team,

I have 700 rows of data which is basically date column, I have to create a new column where it gives output as 1 for 1st 7 rows and followed by 2 for next 7 rows and goes on till 52 and after that it should again start from 1.

Thanks

[Greg_Deckler]

@raassd Try this:

WeekNum = 
    VAR __Count = COUNTROWS( FILTER( 'Dates', [Date] <= EARLIER( 'Dates'[Date] ) ) )
    VAR __Num = INT( DIVIDE( __Count , 7 ) )
    VAR __Result = IF( MOD( __Count, 7 ) = 0, __Num, __Num + 1 )
RETURN
    __Result

[raassd]

@Greg_Deckler ,

Thanks for the response !

Here is my output, I am getting 53 and 54 so but my requirment is after 52 it should start again from 1.

Thanks again!

[Greg_Deckler · Accepted solution]

@raassd Sorry, missed that. This should work:

WeekNum = 
    VAR __Count = COUNTROWS( FILTER( 'Dates', [Date] <= EARLIER( 'Dates'[Date] ) ) )
    VAR __Num = INT( DIVIDE( __Count , 7 ) )
    VAR __WeekNum = IF( MOD( __Count, 7 ) = 0, __Num, __Num + 1 )
    VAR __Result = IF( __WeekNum > 52, __WeekNum - 52 * ( INT( DIVIDE( __WeekNum, 52 ) ) ), __WeekNum )
RETURN
    __Result