Rolling Average on Sprints for total story points covered per sprint

[Iamadityarajput]

Hi,

I have a table and it contains folowing columns 

Issue Key

Product

Sprint Name

Story Points

sprintEnddate

Issue Type

I want to calculated the rolling average of total story points completed per sprint for a period of 3 consecutive sprints and sprints are supposed to be arranged in ascending order by their enddate.

Sample table

Issue Key	Product	Sprint Name	Story Points	sprintEnddate	Issue Type
DOW-102	DOW	DOW1	0	2/8/2023	sub task
DOW-103	DOW	DOW1	5	2/8/2023	task
DOW-101	DOW	DOW2	3	2/15/2023	defect
DOW-105	DOW	DOW2	5	2/15/2023	task
DOW-106	DOW	DOW2	2	2/15/2023	subtask
DOW-1009	DOW	DOW3	8	2/22/2023	defect
DOW-3102	DOW	DOW3	13	2/22/2023	epic
DOW-1012	DOW	DOW2	0	2/15/2023	epic
MNB-3042	MNB	MNB12-19	0	2/8/2023	sub task
MNB-3104	MNB	MNB12-19	5	3/8/2023	task
MNB-2304	MNB	MNB19-25	3	3/15/2023	defect
MNB-34	MNB	MNB19-25	5	3/15/2023	task
MNB-30	MNB	MNB19-25	2	3/15/2023	subtask
MNB-4	MNB	MNB19-25	8	3/22/2023	defect
MNB-3564	MNB	MNB25-2	13	3/29/2023	epic
MNB-324	MNB	MNB25-2	0	3/29/2023	epic
MNB-3224	MNB	MNB25-2	0	3/29/2023	sub task
AQ-2	AQ	AQ1	5	4/15/2023	task
AQ-1283	AQ	AQ1	3	4/15/2023	defect
AQ-3494	AQ	AQ1	5	4/15/2023	task
AQ-31256	AQ	AQ1	2	4/15/2023	subtask
AQ-3212	AQ	AQ101	8	4/25/2023	defect
AQ-320	AQ	AQ101	13	4/25/2023	epic
AQ-326	AQ	AQ101	0	4/25/2023	epic
AQ-322	AQ	AQ2	3	5/5/2023	epic
AQ-1132	AQ	AQ2	5	5/5/2023	epic

[v-yangliu-msft · Accepted solution]

Hi  @Iamadityarajput ,

Here are the steps you can follow：

1. In Power Query -- add Column – Index Column – From 1.

2. Create calculated column.

Rank =
RANKX(
    FILTER(ALL('Sample table'),
    'Sample table'[Sprint Name]=EARLIER('Sample table'[Sprint Name])),[sprintEnddate],,DESC,Dense)

Index_2 =
RANKX(
    FILTER(ALL('Sample table'),
     'Sample table'[Sprint Name]=EARLIER('Sample table'[Sprint Name])&&
    'Sample table'[sprintEnddate]=
MINX(
        FILTER(ALL('Sample table'),
        'Sample table'[Sprint Name]=EARLIER('Sample table'[Sprint Name])),[sprintEnddate])),[Index],,ASC)

3. Create measure.

True =
SUMX(
    FILTER(ALL('Sample table'),
    'Sample table'[Sprint Name]=MAX('Sample table'[Sprint Name])&&
   'Sample table'[Rank]<=4),'Sample table'[Story Points])

True  2 =
var _index=
FILTER(
   ALL('Sample table'),
[Index_2]=1)
var _table=
ADDCOLUMNS(
    _index,"measure",[True])
var _table1=
ADDCOLUMNS(
    _table,"result",
 AVERAGEX(
    FILTER(
        _table,
        [sprintEnddate]<=EARLIER([sprintEnddate])),[measure]))  
return
SUMX(
    FILTER(
        _table1,
        [sprintEnddate]=MAX('Sample table'[sprintEnddate])&&[Sprint Name]=MAX('Sample table'[Sprint Name])),[result])

Flag =
IF(
    [True  2]=BLANK(),0,1)

4. Place [Flag]in Filters, set is=1, apply filter.

5. Result:

Best Regards,

Liu Yang

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

[lbendlin]

What would be the expected outcome based on the sample data you provided?

[Iamadityarajput]

Hi @lbendlin ,

I was looking to plot a line and stacked column chart where on x-axis sprints are arranged in descending order of their completion date and on y-axis we have sum of story points completed during those sprints and a line representing rolling average over the period of 4 sprints(it can be variable).

I am trying to create a measure for that.

[lbendlin]

where on x-axis sprints are arranged in descending order of their completion date 

I think you mean ascending order?

a line representing rolling average over the period of 4 sprints(it can be variable).

I am still unclear what you mean by that. Please elaborate.

see attached for the first part. Note that it requires to add the sort column in Power Query as it would result in a circular dependency if it were done in Power BI.

[Iamadityarajput]

yes, in ascending order(typo),

 rolling( moving ) average i.e., sum of story points completed in previous 4 sprints / no. of sprints(4) period could be variable(may increase or decrease), 

Note that it requires to add the sort column in Power Query as it would result in a circular dependency if it were done in Power BI.

do you mean sorted by sprint?