Forum Discussion
3 sprint rolling average for throughput
Hi everyone,
I'm new in this community and I've been struggling for a couple of weeks because I'm stuck in this problem. My goal is to calculate a 3 sprint rolling average for the sum of story-points completed. I'm not sure if a DAX measure will work or rather a calculated column. I'm open to any workarounds.
Data tables
SprintReportIssues which is the principal table I'm using to produce all the visuals
DateSprint which is a reference table I created for filtering/labeling purposes
Boards which I'm using for the slicer and to filter by BOARD_NAME
I've produced some images to explain the problem
SprintReportIssues
The data has many boards (BOARD_ID and BOARD_NAME), that are composed by many sprints (SPRINT_ID and SPRINT_NAME) which have many issues (ISSUE_ID and ISSUE_KEY). POINTS COMPLETED is a measure I created to calculate the sum of CURRENT_ESTIMATE if SPRINT_REPORT_STATUS = "COMPLETED'
DateSprint
I'm using this table to group sprints by their dates (DATE). In DateSprint, DATE = START_DATE in SprintReportIssues. REFERENCE is just my preferred way of labeling
Matrix of Interest
You can see that the visual is summarizing POINTS COMPLETED by:
Rows: SPRINT ('DateSprint'[REFERENCE]) and SPRINT_NAME ('SprintReportIssues'[SPRINT_NAME])
Values: START_DATE ('DateSprint'[DATE]) which I use to sort and POINTS COMPLETED('SprintReportIssues'[POINTS COMPLETED])
Expected Outcome 1
I want a 3-sprint Rolling Average per REFERENCE. Usually sprints are 15 days so we'd be looking at the current sprint that we want to calculate and ~30 days back
Expected Outcome 2
Ideally, with this Rolling Average I'd be able to filter by BOARD_NAME using the slicer and we'd be able to see Rolling Averages a level down (per board)
Complexity
There are 36 boards and 871 sprints for now. In the images above the measures are grouped by board but in the future I'm looking to have the capability of grouping boards by some other reference that I will call "X" for now. Hierarchy:
Highest Level Lowest Level
X > Boards > Sprints
Sample Data
Reminder: POINTS COMPLETED is a measure
| SPRINT | SPRINT_NAME | Earliest DATE | POINTS COMPLETED |
| 2023.01.3 | 2023.01.3 - Analytics | 2/8/2023 0:00 | 21 |
| 2023.01.3 | 2023.01.3 - Data Product | 2/8/2023 0:00 | 18 |
| 2023.01.3 | 2023.01.3 - EDW Replatform | 2/8/2023 0:00 | 75 |
| 2023.01.3 | 2023.01.3 - On Prem Data WH | 2/8/2023 0:00 | 6 |
| 2023.01.3 | 2023.01.3 -Data Mgmt & Arch | 2/8/2023 0:00 | 26 |
| 2023.01.2 | 2023.01.2 - Analytics | 1/25/2023 0:00 | 18 |
| 2023.01.2 | 2023.01.2 - Data Product | 1/25/2023 0:00 | 18 |
| 2023.01.2 | 2023.01.2 - EDW Replatform | 1/25/2023 0:00 | 91 |
| 2023.01.2 | 2023.01.2 - On Prem Data WH | 1/25/2023 0:00 | 13 |
| 2023.01.2 | 2023.01.2 -Data Mgmt & Arch | 1/25/2023 0:00 | 40 |
| 2023.01.1 | 2023.01.1 - Analytics | 1/11/2023 0:00 | 22 |
| 2023.01.1 | 2023.01.1 - Data Product | 1/11/2023 0:00 | 18 |
| 2023.01.1 | 2023.01.1 - EDW Replatform | 1/11/2023 0:00 | 84 |
| 2023.01.1 | 2023.01.1 - On Prem Data WH | 1/11/2023 0:00 | 24 |
| 2023.01.1 | 2023.01.1 -Data Mgmt & Arch | 1/11/2023 0:00 | 6 |
| PI6 - IP | PI6 - IP - Analytics | 12/28/2022 0:00 | 12 |
| PI6 - IP | PI6 - IP - Data Mgmt & Arch | 12/28/2022 0:00 | 12 |
| PI6 - IP | PI6 - IP - EDW Replatform | 12/28/2022 0:00 | 112 |
| PI6 - IP | PI6 - IP - On-Prem Data WH | 12/28/2022 0:00 | 15 |
| PI6 - IP | PI6 - IP- Data Product | 12/28/2022 0:00 | 26 |
| PI6 - S5 | PI6 - S5 - Analytics | 12/14/2022 0:00 | 7 |
| PI6 - S5 | PI6 - S5 - Data Mgmt & Arch | 12/14/2022 0:00 | 11 |
| PI6 - S5 | PI6 - S5 - EDW Replatform | 12/14/2022 0:00 | 19 |
| PI6 - S5 | PI6 - S5 - On-Prem Data WH | 12/14/2022 0:00 | 10 |
| PI6 - S5 | PI6 - S5- Data Product | 12/14/2022 0:00 | 13 |
| PI6 - S4 | PI6 - S4 - Data Mgmt & Arch | 12/1/2022 0:00 | 15 |
| PI6 - S4 | PI6 - S4 - Analytics | 12/1/2022 0:00 | 27 |
| PI6 - S4 | PI6 - S4 - EDW Replatform | 12/1/2022 0:00 | 50 |
| PI6 - S4 | PI6 - S4 - On-Prem Data WH | 12/1/2022 0:00 | 24 |
| PI6 - S4 | PI6 - S4- Data Product | 12/1/2022 0:00 | 8 |
| PI6 - S3 | PI6 - S3 - Data Mgmt & Arch | 11/16/2022 0:00 | 3 |
| PI6 - S3 | PI6 - S3 - EDW Replatform | 11/16/2022 0:00 | 60 |
| PI6 - S3 | PI6 - S3 - On-Prem Data WH | 11/16/2022 0:00 | 28 |
| PI6 - S3 | PI6 - S3- Analytics | 11/16/2022 0:00 | 16 |
| PI6 - S3 | PI6 - S3- Data Product | 11/16/2022 0:00 | 9 |
| PI6 - S2 | PI6 - S2 - Data Mgmt & Arch | 11/2/2022 0:00 | |
| PI6 - S2 | PI6 - S2 - Analytics | 11/2/2022 0:00 | 14 |
| PI6 - S2 | PI6 - S2 - EDW Replatform | 11/2/2022 0:00 | 25 |
| PI6 - S2 | PI6 - S2 - On-Prem Data WH | 11/2/2022 0:00 | 4 |
| PI6 - S2 | PI6 - S2- Data Product | 11/2/2022 0:00 | 17 |
| PI6 - S1 | PI6 - S1 - Data Mgmt & Arch | 10/19/2022 0:00 | 5 |
| PI6 - S1 | PI6 - S1 - Analytics | 10/19/2022 0:00 | 6 |
| PI6 - S1 | PI6 - S1 - EDW Replatform | 10/19/2022 0:00 | 36 |
| PI6 - S1 | PI6 - S1 - On-Prem Data WH | 10/19/2022 0:00 | 9 |
| PI6 - S1 | PI6 - S1- Data Product | 10/19/2022 0:00 | 19 |
Please help! I've been stuck for so long in this problem and I'm not sure if it cannot be solved because of the sprint order and the dates, or if there's another impediment.
3 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
