Forum Discussion
Solved
Creating metric from multiple field that updates on a regular schedule
I am trying to create a metric called Velocity that is the average completed work over a 6 sprint period for a team. I need to create this at the team level so that it can be used for other reports f...
Hi Shadd307 ，
I am not sure if I understood your question correctly. Do you have up to 6 sprint period per team in your data? Then you need to calculate the average number of issues completed in those periods. If so, please create the new table.
Table 2 = SUMMARIZE ( 'Table', 'Table'[team_name], "Velocity", DIVIDE ( CALCULATE ( COUNT ( 'Table'[issues ID] ), 'Table'[completed before sprint end] = "Complete" ), DISTINCTCOUNT ( 'Table'[sprint.sequence] ) ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
So I cannot figure out how to add the pbix file, zip file. or xlxs file to my post and when i just copy the few rows from the Sprint tables it exceeds the character limit. I reduced it down hope this helps.
| issues ID | issue_key | project | Project name | sprint.id | sprint.name | sprint state | sprint.endDate | sprint.sequence | sprint.startDate | sprint.completionDate | program sprint name | completed before sprint end | committed/carryover | todays_date | current Sprint | done_date | story_points | team_name |
| 6696737 | GDES-116697 | GDES | GDE Project | 105545 | GDE Sprint 10.1 | Closed | 6/8/2022 | 105803 | 5/26/2022 | 6/8/2022 | Sprint 10.1 | Complete | Committed | 0 | 5/26/2022 | 9 | Buy | |
| 6706740 | GDES-117428 | GDES | GDE Project | 105545 | GDE Sprint 10.1 | Closed | 6/8/2022 | 105803 | 5/26/2022 | 6/8/2022 | Sprint 10.1 | Complete | Committed | 0 | 5/27/2022 | 10 | Buy | |
| 6746749 | GDES-118455 | GDES | GDE Project | 103404 | GDE Sprint 9.9 | Closed | 5/25/2022 | 105802 | 5/12/2022 | 5/25/2022 | Sprint 9.9 | Complete | Committed | 0 | 5/12/2022 | 3.5 | Buy | |
| 6756752 | GDES-119553 | GDES | GDE Project | 103404 | GDE Sprint 9.9 | Closed | 5/25/2022 | 105802 | 5/12/2022 | 5/25/2022 | Sprint 9.9 | Complete | CarryOver | 0 | 5/13/2022 | 10 | Buy | |
| 6796762 | GDES-116697 | GDES | GDE Project | 102690 | GDE Sprint 9.8 | Closed | 5/11/2022 | 105801 | 4/28/2022 | 5/11/2022 | Sprint 9.8 | Complete | Committed | 0 | 4/28/2022 | 1 | Buy | |
| 6806764 | GDES-117428 | GDES | GDE Project | 102690 | GDE Sprint 9.8 | Closed | 5/11/2022 | 105801 | 4/28/2022 | 5/11/2022 | Sprint 9.8 | Complete | CarryOver | 0 | 4/29/2022 | 1 | Buy | |
| 6846774 | GDES-118455 | GDES | GDE Project | 101068 | GDE Sprint 9.7 | Closed | 4/27/2022 | 105800 | 4/14/2022 | 4/27/2022 | Sprint 9.7 | Complete | CarryOver | 0 | 4/14/2022 | 9 | Buy | |
| 6856776 | GDES-119553 | GDES | GDE Project | 101068 | GDE Sprint 9.7 | Closed | 4/27/2022 | 105800 | 4/14/2022 | 4/27/2022 | Sprint 9.7 | Complete | CarryOver | 0 | 4/15/2022 | 5 | Buy | |
| 6926793 | GDES-116427 | GDES | GDE Project | 99446 | GDE Sprint 9.6 | Closed | 4/13/2022 | 105799 | 4/1/2022 | 4/13/2022 | Sprint 9.6 | Complete | Committed | 0 | 4/4/2022 | 7 | Buy | |
| 6936796 | GDES-117437 | GDES | GDE Project | 99446 | GDE Sprint 9.6 | Closed | 4/13/2022 | 105799 | 4/1/2022 | 4/13/2022 | Sprint 9.6 | Complete | CarryOver | 0 | 4/5/2022 | 13 | Buy | |
| 6956800 | GDES-119553 | GDES | GDE Project | 97824 | GDE Sprint 9.5 | Closed | 3/30/2022 | 105798 | 3/17/2022 | 3/30/2022 | Sprint 9.5 | Complete | Committed | 0 | 3/18/2022 | 3 | Buy | |
| 6966803 | GDES-116698 | GDES | GDE Project | 97824 | GDE Sprint 9.5 | Closed | 3/30/2022 | 105798 | 3/17/2022 | 3/30/2022 | Sprint 9.5 | Complete | Committed | 0 | 3/19/2022 | 8 | Buy | |
| 6976805 | GDES-117643 | GDES | GDE Project | 97824 | GDE Sprint 9.5 | Closed | 3/30/2022 | 105798 | 3/17/2022 | 3/30/2022 | Sprint 9.5 | Complete | Committed | 0 | 3/20/2022 | 8 | Buy | |
| 6694275 | GDES-117060 | GDES | GDE Project | 105545 | GDE Sprint 10.1 | Closed | 6/8/2022 | 105803 | 5/26/2022 | 6/8/2022 | Sprint 10.1 | Complete | CarryOver | 0 | 5/28/2022 | 5 | Pricing | |
| 6694276 | GDES-117050 | GDES | GDE Project | 105545 | GDE Sprint 10.1 | Closed | 6/8/2022 | 105803 | 5/26/2022 | 6/8/2022 | Sprint 10.1 | Complete | Committed | 0 | 5/29/2022 | 1 | Pricing | |
| 6694278 | GDES-117030 | GDES | GDE Project | 103404 | GDE Sprint 9.9 | Closed | 5/25/2022 | 105802 | 5/12/2022 | 5/25/2022 | Sprint 9.9 | Complete | Committed | 0 | 5/12/2022 | 4 | Pricing | |
| 6694279 | GDES-117020 | GDES | GDE Project | 103404 | GDE Sprint 9.9 | Closed | 5/25/2022 | 105802 | 5/12/2022 | 5/25/2022 | Sprint 9.9 | Complete | CarryOver | 0 | 5/13/2022 | 9 | Pricing | |
| 6694286 | GDES-116951 | GDES | GDE Project | 102690 | GDE Sprint 9.8 | Closed | 5/11/2022 | 105801 | 4/28/2022 | 5/11/2022 | Sprint 9.8 | Complete | Committed | 0 | 5/1/2022 | 13 | Pricing | |
| 6694287 | GDES-116941 | GDES | GDE Project | 102690 | GDE Sprint 9.8 | Closed | 5/11/2022 | 105801 | 4/28/2022 | 5/11/2022 | Sprint 9.8 | Complete | Committed | 0 | 5/2/2022 | 10 | Pricing | |
| 6694288 | GDES-116932 | GDES | GDE Project | 101068 | GDE Sprint 9.7 | Closed | 4/27/2022 | 105800 | 4/14/2022 | 4/27/2022 | Sprint 9.7 | Complete | CarryOver | 0 | 4/14/2022 | 9 | Pricing | |
| 6694290 | GDES-116912 | GDES | GDE Project | 101068 | GDE Sprint 9.7 | Closed | 4/27/2022 | 105800 | 4/14/2022 | 4/27/2022 | Sprint 9.7 | Complete | Committed | 0 | 4/16/2022 | 4 | Pricing | |
| 6694295 | GDES-116863 | GDES | GDE Project | 99446 | GDE Sprint 9.6 | Closed | 4/13/2022 | 105799 | 4/1/2022 | 4/13/2022 | Sprint 9.6 | Complete | Committed | 0 | 4/3/2022 | 2 | Pricing | |
| 6694297 | GDES-116843 | GDES | GDE Project | 99446 | GDE Sprint 9.6 | Closed | 4/13/2022 | 105799 | 4/1/2022 | 4/13/2022 | Sprint 9.6 | Complete | CarryOver | 0 | 4/5/2022 | 5 | Pricing | |
| 6694298 | GDES-116833 | GDES | GDE Project | 97824 | GDE Sprint 9.5 | Closed | 3/30/2022 | 105798 | 3/17/2022 | 3/30/2022 | Sprint 9.5 | Complete | CarryOver | 0 | 3/17/2022 | 1 | Pricing | |
| 6694299 | GDES-116823 | GDES | GDE Project | 97824 | GDE Sprint 9.5 | Closed | 3/30/2022 | 105798 | 3/17/2022 | 3/30/2022 | Sprint 9.5 | Complete | Committed | 0 | 3/18/2022 | 1 | Pricing | |
| 6694301 | GDES-116803 | GDES | GDE Project | 97824 | GDE Sprint 9.5 | Closed | 3/30/2022 | 105798 | 3/17/2022 | 3/30/2022 | Sprint 9.5 | Complete | Committed | 0 | 3/20/2022 | 9 | Pricing |