Forum Discussion

JeevanMallya's avatar
JeevanMallya
Icon for Resolver II rankResolver II
1 year ago
Solved

Need Help in Building Release Burn up Predicted Cumulative Completed StoryPoint for Future Sprints

I have a RELEASE_BURN_UP Table used for Building Release Burn up.

SPRINT_NAMESPRINT_NUABCERFIX_VERSIONSTART_DATEEND_DATECOMPLETE_DATESTORY_POINTS_COMMITTEDSTORY_POINTS_COMPLETEDSTORY_POINTS_REMAININGSPRINT_DURATION
ABC Sprint 000022.1111-07-202201-08-202201-08-20221914516
ABC Sprint 010122.1101-08-202222-08-202222-08-20221501516
ABC Sprint 030322.1112-09-202203-10-202203-10-202203-316
ABC Sprint 050522.1128-10-202212-11-202214-11-202203-311
ABC Sprint 060623.0314-11-202202-12-202207-12-202260615
ABC Sprint 070723.0309-12-202224-12-202226-12-2022129311
ABC Sprint 101023.0307-02-202328-02-202328-02-202304-416
ABC Sprint 080823.0326-12-202214-01-202317-01-20231011-115
ABC Sprint 111123.0328-02-202320-03-202324-03-202358-315
ABC Sprint 090923.0318-01-202307-02-202307-02-202363315
ABC Sprint 272724.0312-02-202401-03-202404-03-20246.55.5115
ABC Sprint 070724.0309-12-202224-12-202226-12-202230311
ABC Sprint 232324.0317-11-202308-12-202307-12-20231412216
ABC Sprint 313124.1106-05-202424-05-202427-05-202463315
ABC Sprint 333324.1117-06-202406-07-202408-07-20241518-315
ABC Sprint 151523.0730-05-202321-06-202320-06-202305-517
ABC Sprint 121223.0724-03-202315-04-202318-04-202370716
ABC Sprint 161623.0720-06-202311-07-202311-07-202305-516
ABC Sprint 141423.0709-05-202329-05-202330-05-20231920-115
ABC Sprint 181823.1101-08-202322-08-202322-08-2023147716
ABC Sprint 191923.1122-08-202312-09-202312-09-20232234-1216
ABC Sprint 202023.1112-09-202303-10-202304-10-2023129316
ABC Sprint 222224.0326-10-202316-11-202316-11-20232118316
ABC Sprint 212123.1104-10-202325-10-202325-10-202345-116
ABC Sprint 242424.0308-12-202329-12-202302-01-20242030-1016
ABC Sprint 252524.0302-01-202419-01-202422-01-20242222014
ABC Sprint 292924.0725-03-202413-04-202412-04-20241922-315
ABC Sprint 303024.0715-04-202403-05-202404-05-20242121015
ABC Sprint 353524.1129-07-202416-08-202420-08-20242021-115
ABC Sprint 131323.0718-04-202309-05-202308-05-202311016
ABC Sprint 171723.1111-07-202302-08-202301-08-202333017
ABC Sprint 262624.0322-01-202409-02-202412-02-20242424015
ABC Sprint 414125.0303-12-202421-12-202423-12-20241923-414
ABC Sprint 282824.0704-03-202422-03-202424-03-202485315
ABC Sprint 323224.1127-05-202414-06-202415-06-202422015
ABC Sprint 404025.0311-11-202429-11-202403-12-20242217515
ABC Sprint 262624.0722-01-202409-02-202412-02-202430315
ABC Sprint 313124.0706-05-202424-05-202427-05-202419.519.5015
ABC Sprint 272723.0712-02-202401-03-202404-03-202411015
ABC Sprint 383824.1130-09-202418-10-202422-10-20247.57.5015
ABC Sprint 282824.0304-03-202422-03-202424-03-202411015
ABC Sprint 343424.1108-07-202426-07-202429-07-20242519615
ABC Sprint 393925.0322-10-202408-11-202411-11-20241611514
ABC Sprint 373724.1110-09-202428-09-202430-09-20241422-814
ABC Sprint 363624.1120-08-202410-09-202409-09-20241916316
ABC Sprint 424225.0323-12-202411-01-202513-01-20251112-115
ABC Sprint 383825.0330-09-202418-10-202422-10-202430315
ABC Sprint 393924.1122-10-202408-11-202411-11-202422014

I also have a ReleaseTable that basically mentions the GOAL for different Releases

FIX_VERSIONRELEASE_START_DATERELEASE_END_DATEGOALRELEASE_STATUSVERSION_TYPEWORKING_DAYSNUMBER_OF_SPRINTS
23.1104-07-202330-10-202358CompletedMajor855
24.0331-10-202325-03-2024117CompletedMajor1057
24.0725-03-202402-07-202476CompletedMinor724
24.1103-07-202405-11-2024111CompletedMajor906
25.0306-11-202425-03-2025100In ProgressMajor1006
25.0726-03-202502-07-2025100PlannedMinor714

 

A calculated FutureSprints table : 

FutureSprints =
ADDCOLUMNS(
    GENERATESERIES(
        MAX('RELEASE_BURN_UP'[SPRINT_NUMBER]) + 1,
        MAX('RELEASE_BURN_UP'[SPRINT_NUMBER]) + 3,
        1
    ),
    "SPRINT_NAME", "ABC Sprint " & [Value]
)
 
AllSprints Table : 
AllSprints =
DISTINCT(
    UNION(
        SELECTCOLUMNS(
            'RELEASE_BURN_UP',
            "SPRINT_NUMBER", 'RELEASE_BURN_UP'[SPRINT_NUMBER],
            "SPRINT_NAME", 'RELEASE_BURN_UP'[SPRINT_NAME]
        ),
        SELECTCOLUMNS(
            FutureSprints,
            "SPRINT_NUMBER", [SPRINT_NUMBER],
            "SPRINT_NAME", FutureSprints[SPRINT_NAME]
        )
    )
)
CumulativeSPCommitted Measure to Calculate Story Points comitted untill the last completed sprint Also CumulativeSPCompleted Measure to Calculate Story Points completed untill the last completed sprint, Also Dynamic Goal Measure : Which bascially looks into ReleaseTable for Goal.
I want to build a burnup chart that shows Predicted Story Points completion for Future sprints.Basically with the current progress when the sprint team can reach the defined goal.I was able to build visual but need help in defining projections. Please suggest.
 
I used Measure : PredictedCumulativeSPCompleted but getting a Flat line as Prediction for Future Sprints (Sprints 42 to 45)
PredictedCumulativeSPCompleted =
VAR LastCompletedSprint =
    CALCULATE(
        MAX('RELEASE_BURN_UP'[SPRINT_NAME]),
        'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED] > 0
    )

VAR CompletedPoints =
    CALCULATE(
        SUM('RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]),
        FILTER(
            ALL('AllSprints'[SPRINT_NAME]),
            'AllSprints'[SPRINT_NAME] <= MAX('AllSprints'[SPRINT_NAME])
        )
    )

VAR GoalValue =
    LOOKUPVALUE(
        ReleaseTable[Goal],
        ReleaseTable[FIX_VERSION], SELECTEDVALUE(RELEASE_BURN_UP[FIX_VERSION])
    )

VAR RemainingPoints = GoalValue - CompletedPoints

VAR AverageStoryPointsPerSprint =
    AVERAGEX(
        FILTER(
            'RELEASE_BURN_UP',
            'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED] > 0
        ),
        'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]
    )

-- Check if future sprints need to be projected
VAR FutureSprints =
    IF(AverageStoryPointsPerSprint > 0,
        DIVIDE(RemainingPoints, AverageStoryPointsPerSprint, 0),
        0
    )

VAR PredictedPointsForFutureSprints =
    AverageStoryPointsPerSprint * FutureSprints

VAR PredictedCumulativeForFuture =
    CompletedPoints + PredictedPointsForFutureSprints

RETURN
IF(
    MAX('AllSprints'[SPRINT_NAME]) <= LastCompletedSprint,
    -- Historical sprints: Cumulative sum of completed points
    CALCULATE(
        SUM('RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]),
        FILTER(
            ALL('AllSprints'[SPRINT_NAME]),
            'AllSprints'[SPRINT_NAME] <= MAX('AllSprints'[SPRINT_NAME])
        )
    ),
    -- Future sprints: Cumulative sum of predicted points
    PredictedCumulativeForFuture
)
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi JeevanMallya  - To project future story point completion and determine when the team will reach the defined goal, we can use a forecasting approach based on the team's velocity

     

    Please find the attached pbix.

     

     

  • JeevanMallya's avatar
    JeevanMallya
    1 year ago

    I was able to achive CumulativeSPCommitted, CumulativeSPCompleted as well. Need help in defining PredictedCumulativeSPCompleted for Future Sprints Sprint 43, Sprint 44, Sprint 45.

    Basically in which Sprint the team will reach the goal based on current progress.

    I was able to achive as shown below by using Measure  but getting Flat line for Future Sprints: 

    PredictedCumulativeSPCompleted =
    VAR LastCompletedSprint =
        CALCULATE(
            MAX('RELEASE_BURN_UP'[SPRINT_NAME]),
            'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED] > 0
        )

    VAR CompletedPoints =
        CALCULATE(
            SUM('RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]),
            FILTER(
                ALL('AllSprints'[SPRINT_NAME]),
                'AllSprints'[SPRINT_NAME] <= MAX('AllSprints'[SPRINT_NAME])
            )
        )

    VAR GoalValue =
        LOOKUPVALUE(
            ReleaseTable[Goal],
            ReleaseTable[FIX_VERSION], SELECTEDVALUE(RELEASE_BURN_UP[FIX_VERSION])
        )

    VAR RemainingPoints = GoalValue - CompletedPoints

    VAR AverageStoryPointsPerSprint =
        AVERAGEX(
            FILTER(
                'RELEASE_BURN_UP',
                'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED] > 0
            ),
            'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]
        )

    -- Check if future sprints need to be projected
    VAR FutureSprints =
        IF(AverageStoryPointsPerSprint > 0,
            DIVIDE(RemainingPoints, AverageStoryPointsPerSprint, 0),
            0
        )

    VAR PredictedPointsForFutureSprints =
        AverageStoryPointsPerSprint * FutureSprints

    VAR PredictedCumulativeForFuture =
        CompletedPoints + PredictedPointsForFutureSprints

    RETURN
    IF(
        MAX('AllSprints'[SPRINT_NAME]) <= LastCompletedSprint,
        -- Historical sprints: Cumulative sum of completed points
        CALCULATE(
            SUM('RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]),
            FILTER(
                ALL('AllSprints'[SPRINT_NAME]),
                'AllSprints'[SPRINT_NAME] <= MAX('AllSprints'[SPRINT_NAME])
            )
        ),
        -- Future sprints: Cumulative sum of predicted points
        PredictedCumulativeForFuture
    )

     

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi JeevanMallya  - To project future story point completion and determine when the team will reach the defined goal, we can use a forecasting approach based on the team's velocity

     

    Please find the attached pbix.

     

     

    • JeevanMallya's avatar
      JeevanMallya
      Icon for Resolver II rankResolver II
      1 year ago

      I was able to achive CumulativeSPCommitted, CumulativeSPCompleted as well. Need help in defining PredictedCumulativeSPCompleted for Future Sprints Sprint 43, Sprint 44, Sprint 45.

      Basically in which Sprint the team will reach the goal based on current progress.

      I was able to achive as shown below by using Measure  but getting Flat line for Future Sprints: 

      PredictedCumulativeSPCompleted =
      VAR LastCompletedSprint =
          CALCULATE(
              MAX('RELEASE_BURN_UP'[SPRINT_NAME]),
              'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED] > 0
          )

      VAR CompletedPoints =
          CALCULATE(
              SUM('RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]),
              FILTER(
                  ALL('AllSprints'[SPRINT_NAME]),
                  'AllSprints'[SPRINT_NAME] <= MAX('AllSprints'[SPRINT_NAME])
              )
          )

      VAR GoalValue =
          LOOKUPVALUE(
              ReleaseTable[Goal],
              ReleaseTable[FIX_VERSION], SELECTEDVALUE(RELEASE_BURN_UP[FIX_VERSION])
          )

      VAR RemainingPoints = GoalValue - CompletedPoints

      VAR AverageStoryPointsPerSprint =
          AVERAGEX(
              FILTER(
                  'RELEASE_BURN_UP',
                  'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED] > 0
              ),
              'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]
          )

      -- Check if future sprints need to be projected
      VAR FutureSprints =
          IF(AverageStoryPointsPerSprint > 0,
              DIVIDE(RemainingPoints, AverageStoryPointsPerSprint, 0),
              0
          )

      VAR PredictedPointsForFutureSprints =
          AverageStoryPointsPerSprint * FutureSprints

      VAR PredictedCumulativeForFuture =
          CompletedPoints + PredictedPointsForFutureSprints

      RETURN
      IF(
          MAX('AllSprints'[SPRINT_NAME]) <= LastCompletedSprint,
          -- Historical sprints: Cumulative sum of completed points
          CALCULATE(
              SUM('RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]),
              FILTER(
                  ALL('AllSprints'[SPRINT_NAME]),
                  'AllSprints'[SPRINT_NAME] <= MAX('AllSprints'[SPRINT_NAME])
              )
          ),
          -- Future sprints: Cumulative sum of predicted points
          PredictedCumulativeForFuture
      )