I want to calculate rolling 3 sprint avg

Power BI
1

hi,
I am trying to calculate rolling 3 sprint avg but somehow the results are not coming accurate. I am attaching a PBIX sample file and the expected result as well.

let me know you needed any other details.

sample rolling 3 sprints.pbix (52.1 KB)

Feel free to modify and give me the correct result.

Name BeginDate Actual Velocity New Rolling 3-Sprint Avg
Sprint 1 (7/6 - 7/19) 07-06-2020 36 230.8333333
Sprint 10 (11/9 - 11/22) 11-09-2020 366.5 292.3333333
Sprint 100 (4/22 - 5/5) 4-22-2024 290 252
Sprint 101 (5/6 - 5/19) 05-06-2024 220.5 227.3333333
Sprint 102 (5/20 - 6/2) 5-20-2024 245.5 222
Sprint 103 (6/3 - 6/16) 06-03-2024 216 205.6666667
Sprint 104 (6/17 - 6/30) 6-17-2024 204.5 200.5
Sprint 105 (7/1 - 7/14) 07-01-2024 196.5 196.5
2

Hi @AlexBehrmann

Just some questions,

  1. What is your logic behind getting the rolling 3 sprint avg? How did you get these numbers?
  2. Beginning date are using this part of the calculation, if so then it need to be in a date format not text format?
    You have year 2020 in your data is this correct?

Have you tried using DataMentor that is built within the EDNA platform?

Sorry for the questions, just trying to understand your data

thanks
Keith

3

Assuming the sprints are properly sorted, this should work:

3-Sprint Rolling Avg= 
AVERAGEX( 
    WINDOW(
        -2, REL,
        0, REL,
        ORDERBY( SPRINTS[Name], ASC)
    ),
    [Actual Velocity New]
)
4

Hi Keith,

  1. Logic is getting last 3 sprints velocity number and dividing it by 3, but the latest sprint is there then it should divide by 1 or 2.
  2. Beginning date is a date only, in sample pbix may be mistakenly it remained text.
  3. NO I haven’t tried DataMentor, will check that.

Thank you,
Alex