How to create an index column for each new cycle

[jmowen]

Hello,

I would like some help with creating a new colunm where an index is given to every new cycle.

A cycle for each ID is complete when Empty = True 

A new cycle starts when Score is added. 

Data

ID	JobType	Date	Score	Full	Score Removed	Empty
1	S	1/02/2021	500	FALSE
1	S	2/02/2021	1500	TRUE
1	H	5/05/2021			2000	TRUE
1	S	7/08/2021	250	FALSE
1	S	8/08/2021	1000	FALSE
1	S	9/08/2021	500	TRUE
1	H	10/12/2021			1000	FALSE
1	H	11/12/2021			750	TRUE
1	S	12/12/2021	1600	TRUE
2	S	5/02/2021	500	FALSE
2	S	6/02/2021	1000	TRUE
2	H	17/05/2021			1500	TRUE
2	S	19/05/2021	1500	TRUE
3	S	1/02/2021	1750	TRUE
3	H	4/05/2021			1000	FALSE
3	H	5/05/2021			750	TRUE

Expected Outcome

ID	JobType	Date	Score	Full	Score Removed	Empty	Cycle Index
1	S	1/02/2021	500	FALSE			1
1	S	2/02/2021	1500	TRUE			1
1	H	5/05/2021			2000	TRUE	1
1	S	7/08/2021	250	FALSE			2
1	S	8/08/2021	1000	FALSE			2
1	S	9/08/2021	500	TRUE			2
1	H	10/12/2021			1000	FALSE	2
1	H	11/12/2021			750	TRUE	2
1	S	12/12/2021	1600	TRUE			3
2	S	5/02/2021	500	FALSE			4
2	S	6/02/2021	1000	TRUE			4
2	H	17/05/2021			1500	TRUE	4
2	S	19/05/2021	1500	TRUE			5
3	S	1/02/2021	1750	TRUE			6
3	H	4/05/2021			1000	FALSE	6
3	H	5/05/2021			750	TRUE	6

This is so I can summerise the data as shown below

ID	Score	Score Removed	Cycle Complete	Cycle Start Date	Cycle End Date	Cycle Index
1	2000	2000	TRUE	1/02/2021	5/05/2021	1
1	1750	1750	TRUE	7/08/2021	7/08/2021	2
1		1600	FALSE	12/12/2021		3
2	1500	1500	TRUE	5/02/2021	17/05/2021	4
2		1500	FALSE	19/05/2021		5
3	1750	1750	TRUE	1/02/2021	5/05/2021	6

[v-jayw-msft · Accepted solution]

Hi @jmowen ,

Create a index column in Power Query Editor. Then create a calculated column as below:

Column = 
var index2 = CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Index]>=EARLIER('Table'[Index])&&'Table'[Empty]=TRUE()))
return
IF(ISBLANK(index2),'Table'[Index],index2)

Then create the Cycle Index like below:

rank_ = RANKX('Table','Table'[Column],,ASC,Dense)

Best Regards,

Jay