Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
JeevanMallya
Resolver II
Resolver II

Need Help in Building Projected ProjectedSPCompletion for Release

‎03-04-2025 08:52 PM

I have two tabels RELEASE_BURN_UP and ReleaseTable. RELEASE_BURN_UP contains all Sprint Data for Speicific Version (FIX_VERSION) and ReleaseTable contains Release details. I want to show Projections (When the Team will be able to achive the Goal) for RELEASE_STATUS = In Progress. In this case it is 25.07.The Ones that are completed (25.03, 24.11, 24.07, 24.03, 23.11) should not be affected since the Release is completed. Projections should be shown only for the ones that Team is working now.

Using Line chart with Axis as shown below

2025-03-05 10_09_18-BLR_R&D - WCR_Redeye.png

The RELEASE_BURN_UP Table is as shown below

SPRINT_NAMESPRINT_NUMBERFIX_VERSIONSTART_DATEEND_DATECOMPLETE_DATESTORY_POINTS_COMMITTEDSTORY_POINTS_COMPLETEDSTORY_POINTS_REMAININGSPRINT_DURATION
Sprint 17817822.1108-08-202226-08-202227-08-202230315
Sprint 18018022.1119-09-202207-10-202209-10-2022611-515
Sprint 18218223.0331-10-202218-11-202220-11-2022013-1315
Sprint 18118123.0310-10-202228-10-202229-10-202208-815
Sprint 19219223.0705-06-202323-06-202324-06-2023067-6715
Sprint 19119123.0715-05-202302-06-202303-06-20233835315
Sprint 19319323.1126-06-202314-07-202316-07-202306-615
Sprint 18318323.0321-11-202209-12-202210-12-2022013-1315
Sprint 18418423.0311-12-202206-01-202308-01-20232022-220
Sprint 18518523.0309-01-202327-01-202329-01-2023024-2415
Sprint 18618623.0330-01-202317-02-202318-02-2023010-1015
Sprint 18718723.0720-02-202310-03-202311-03-2023018-1815
Sprint 18818823.0713-03-202303-04-202302-04-2023021-2116
Sprint 19819824.0309-10-202327-10-202330-10-20232571815
Sprint 19619623.1128-08-202315-09-202317-09-20235353015
Sprint 19919924.0330-10-202317-11-202318-11-2023528-2315
Sprint 19519523.1107-08-202325-08-202327-08-2023023-2315
Sprint 19419423.1117-07-202304-08-202305-08-2023028-2815
Sprint 19419423.0717-07-202304-08-202305-08-2023010-1015
Sprint 20020024.0320-11-202308-12-202310-12-202305-515
Sprint 20320324.0329-01-202416-02-202419-02-2024044-4415
Sprint 20120124.0311-12-202305-01-202408-01-2024210-820
Sprint 19919924.0730-10-202317-11-202318-11-202330315
Sprint 20620624.0701-04-202419-04-202421-04-2024158715
Sprint 20220224.0308-01-202426-01-202428-01-20242202215
Sprint 20420424.0719-02-202408-03-202411-03-202440415
Sprint 20520524.0711-03-202429-03-202431-03-202404-415
Sprint 20720724.0722-04-202417-05-202420-05-20241624-820
Sprint 21121124.1123-07-202409-08-202411-08-2024925-1614
Sprint 21321324.1102-09-202420-09-202422-09-20241431115
Sprint 21421424.1123-09-202411-10-202414-10-2024024-2415
Sprint 20820824.0720-05-202407-06-202410-06-202409-915
Sprint 20920924.1110-06-202428-06-202430-06-20241101115
Sprint 21021024.1101-07-202419-07-202423-07-202441315
Sprint 21221224.1112-08-202430-08-202401-09-202450515
Sprint 21521525.0314-10-202404-11-202402-11-20241301316
Sprint 21621625.0304-11-202422-11-202424-11-202402-215
Sprint 22022025.0301-02-202521-02-202522-02-2025016-1615
Sprint 21721725.0325-11-202413-12-202414-12-202423-115
Sprint 21721725.0725-11-202413-12-202414-12-202460615
Sprint 21921925.0312-01-202531-01-202501-02-202552315
Sprint 168168null10-01-202228-01-202229-01-202230315
Sprint 206206null01-04-202419-04-202421-04-202420215
Sprint 210210null01-07-202419-07-202423-07-202420215
Sprint 213213null02-09-202420-09-202422-09-202420215

 

The ReleaseTable is as shown below

FIX_VERSIONRELEASE_START_DATERELEASE_END_DATEGOALRELEASE_STATUSVERSION_TYPEWORKING_DAYSNUMBER_OF_SPRINTS
23.1104-07-202330-10-2023120CompletedMajor855
24.0331-10-202325-03-2024100CompletedMajor1057
24.0725-03-202402-07-202450CompletedMinor724
24.1103-07-202405-11-202460CompletedMajor906
25.0306-11-202425-03-202530CompletedMajor1006
25.0726-03-202502-07-202550In ProgressMinor714

 

Need Measures or powerquery that i can use to build these.

The Measures i am currently using now are

CumulativeSPCommitted =
CALCULATE(
    SUM('RELEASE_BURN_UP'[STORY_POINTS_COMMITTED]),
    FILTER(
        ALLSELECTED('RELEASE_BURN_UP'),
        'RELEASE_BURN_UP'[FIX_VERSION] = MAX('RELEASE_BURN_UP'[FIX_VERSION]) &&
        'RELEASE_BURN_UP'[SPRINT_NAME] <= MAX('RELEASE_BURN_UP'[SPRINT_NAME])
    )
)
 
CumulativeSPCompleted =
CALCULATE(
    SUM('RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]),
    FILTER(
        ALLSELECTED('RELEASE_BURN_UP'),
        'RELEASE_BURN_UP'[FIX_VERSION] = MAX('RELEASE_BURN_UP'[FIX_VERSION]) &&
        'RELEASE_BURN_UP'[SPRINT_NAME] <= MAX('RELEASE_BURN_UP'[SPRINT_NAME])
    )
)
 
DynamicGoal =
VAR GoalValue =
    LOOKUPVALUE(
        ReleaseTable[GOAL],
        ReleaseTable[FIX_VERSION], SELECTEDVALUE(RELEASE_BURN_UP[FIX_VERSION])
    )
VAR DefaultGoal = 100  -- Default goal value when GoalValue is blank
RETURN
    IF(
        ISBLANK(GoalValue),
        DefaultGoal,  -- Return DefaultGoal when the goal is not defined
        GoalValue  -- Return the actual GoalValue when it's defined
    )
 
ProjectedSPCompletion =
VAR InProgressFixVersion =
    SELECTEDVALUE(ReleaseTable[FIX_VERSION])

VAR CompletedSprints =
    FILTER(
        'RELEASE_BURN_UP',
        'RELEASE_BURN_UP'[FIX_VERSION] = InProgressFixVersion &&
        'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED] > 0
    )

VAR SprintCount = COUNTROWS(CompletedSprints)

VAR AvgVelocity =
    IF(
        SprintCount > 1,
        AVERAGEX(CompletedSprints, 'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]),
        IF(
            SprintCount = 1,
            MAXX(CompletedSprints, 'RELEASE_BURN_UP'[STORY_POINTS_COMPLETED]),
            BLANK()  -- No completed sprints with story points
        )
    )

VAR LastSprintNumber =
    MAXX(
        FILTER(
            ALL('RELEASE_BURN_UP'),
            'RELEASE_BURN_UP'[FIX_VERSION] = InProgressFixVersion
        ),
        'RELEASE_BURN_UP'[SPRINT_NUMBER]
    )

VAR CurrentSprintNumber = MAX('RELEASE_BURN_UP'[SPRINT_NUMBER])

VAR GoalSP =
    LOOKUPVALUE(ReleaseTable[GOAL], ReleaseTable[FIX_VERSION], InProgressFixVersion)

VAR RemainingSprints =
    COUNTROWS(
        FILTER(
            'RELEASE_BURN_UP',
            'RELEASE_BURN_UP'[FIX_VERSION] = InProgressFixVersion &&
            'RELEASE_BURN_UP'[SPRINT_NUMBER] > LastSprintNumber
        )
    )

VAR DefaultVelocity =
    IF(
        NOT ISBLANK(GoalSP) && RemainingSprints > 0,
        GoalSP / (RemainingSprints + 1),  -- Spread goal across remaining sprints
        BLANK()
    )

VAR FinalVelocity =
    IF(
        ISBLANK(AvgVelocity) || AvgVelocity = 0,
        DefaultVelocity,
        AvgVelocity
    )

RETURN
IF(
    NOT ISBLANK(InProgressFixVersion) &&
    CurrentSprintNumber > LastSprintNumber,
    [CumulativeSPCompleted] + ((CurrentSprintNumber - LastSprintNumber) * FinalVelocity),
    [CumulativeSPCompleted]
)
 
Labels:
Message 1 of 5
280 Views
0
4 REPLIES 4
v-csrikanth
Community Support
Community Support

‎07-10-2025 12:14 AM

Hi @JeevanMallya 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

Message 5 of 5
79 Views
0
v-csrikanth
Community Support
Community Support

‎06-06-2025 12:03 AM

Hi @JeevanMallya 

Thank you for being part of the Microsoft Fabric Community.

As highlighted by @Dangar332 , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

Message 4 of 5
120 Views
0
JeevanMallya
Resolver II
Resolver II

‎03-04-2025 10:00 PM

I am not able to show Projections for Current Release which is In Progress (25.07). Some how need to add few Future Sprints and show the projections based on Velocity. Here as you see the Goal is to achieve 50 story points in the Release and based on past velocity, i would need projections that will show you need X sprints to achieve this goal in this Release.

2025-03-05 11_29_18-BLR_R&D - WCR_Redeye.png

Message 3 of 5
249 Views
0
Dangar332
Super User
Super User

‎03-04-2025 09:56 PM

Hi, @JeevanMallya 

 

Can you please clarify Where do you stuck? Not get idea from above description.

Message 2 of 5
252 Views
0

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All