Calculating cumulative total of a column from a specified index

[Anony_mous]

Hi All,

I am new to power bi I got a requirement like I need to start the logic from a specified index say for example I have 3 columns
Month, Positions, Rejected.Sample data is shown below:

Month	Positions	Accepted
22-Feb	11
22-Mar	9
22-Apr	28
22-May	40
22-Jun	9	5
22-Jul	1	7
22-Aug	10	7
22-Sep	3	12
22-Oct	94	9
22-Nov	32	16
22-Dec	26	16
23-Jan	46	20
23-Feb	25	26
23-Mar	39	30
23-Apr	7	26
23-May		23
23-Jun		35
23-Jul		26
23-Aug		22
23-Sep
23-Oct
23-Nov
23-Dec

So finally two columns will be added one for Positions column and the other for Rejected column.As we can see the first four rows of both strike and defeat column is blank that is because I have taken the indexing as 4 so it will skip the first 4 rows and start from 5th row similarly if the indexing is 3 it will skip first 3 rows and start indexing from 4th row. Columns Positions,Strike are related it means the 1st row value of positions column is appearing in 5th row of Strike column since we have given the constant indexing as 4.likewise the sum of 1st and 2nd row value of positions column will be the value for 6th row of Strike column (cumulative total).Columns Rejected,Defeat are related it means the 1st row value of Rejected column is appearing in 5th row of Defeat column since we have given the constant indexing as 4.likewise the sum of 1st and 2nd row value of Rejected column will be the value for 6th row of Defeat column (cumulative total).Finally the data will have 2 new columns Strike and Defeat.The data is shown below:

Please give some solutions.All suggestions are welcome thank you in advance!!

Regards,

Anony_mous

Strike	Defeat
11	5
20	12
48	19
88	31
97	40
98	56
108	72
111	92
205	118
237	148
263	174
309	197
334	232
373	258
380	280
380	280
380	280
380	280
380	280

[v-jingzhang · Accepted solution]

Hi @Anony_mous 

First add an Index column to your table with Power Query Editor. 

Then create the following new columns with DAX. I provide the indexing number as a variable in the formula. 

Strike = 
var indexing = 4
return
CALCULATE(SUM('Table'[Positions]),ALL('Table'),'Table'[Index]>=1,'Table'[Index]<=EARLIER('Table'[Index])-indexing)

Defeat = 
var indexing = 4
return
CALCULATE(SUM('Table'[Accepted]),ALL('Table'),'Table'[Index]>=indexing+1,'Table'[Index]<=EARLIER('Table'[Index]))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.