3 sprint rolling average for throughput

[gse08]

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.

[lbendlin]

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-...
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

[gse08]

added data

[lbendlin]

You say you have three tables in that scenario - please provide sample data for the other two.