Forum Discussion
Rolling Average on Sprints for total story points covered per sprint
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 |
Hi Anonymous ,
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
5 Replies
- lbendlin
Super User
What would be the expected outcome based on the sample data you provided?
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 dateI 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.
Hi Anonymous ,
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
