cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## New column where it gives same output for each 7 rows

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

1 ACCEPTED SOLUTION
Super User

@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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Super User

@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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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!

Super User

@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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.