Formula required

[amit_wairkar]

Hi,

I have a following DevOps table. There are two columns that needs to be derived.

1. Actual Story Points:
   1. For a Sprint if all the User Stories are closed, then the Sprint is completed.  Then the Story point is considered for that User Story.
   2. For a Sprint if any of the User Stories is not closed, then Sprint is Active. Then the Story Point is not considered for any of the User Stories. Therefore the output is Zero.
2. Average Story Points: It is the average of all the closed Sprints.

Product	Work Item ID	Sprint	Work Item	Status	Story Points	Actual Story Points	Average Story Points
Gift Voucher	1	Sprint 01	User Story 1	Closed	2	2	5
Gift Voucher	2	Sprint 01	User Story 2	Closed	3	3	5
Gift Voucher	3	Sprint 02	User Story 1	Closed	5	5	5
Demo	4	Sprint 01	User Story 1	Closed	1	0	8
Demo	5	Sprint 01	User Story 2	Open	2	0	8
Demo	4	Sprint 02	User Story 1	Closed	1	1	8
Demo	5	Sprint 02	User Story 2	Closed	2	2	8
Demo	6	Sprint 02	User Story 3	Closed	5	5	8
Demo	7	Sprint 03	User Story 1	New	2	2	8

[Sergii24 · Accepted solution]

Hi @amit_wairkar, so what exactly is your question? What have you tried to do? What is not working? What do you struggle with (hint: everything is not an anwer 😉 ).

Remeber that community is here to help to those who wants to learn, not solving task for you 🙂

[amit_wairkar]

@Sergii24 I have tried a lot of formulas to derive the column for Actual Story Points and Average Story Points. But i couldnt get through. So request your help for building the formula.

Regards,

Amit

[Sergii24 · Accepted solution]

So, first of all you need to understand whether the sprint is active or closed:

Sprint Status = 
VAR _CurrentSprint = 'Table'[Sprint]        //capture sprint from a current row
VAR _OpenUserStories =                      //table that contains all rows for current sprint that are not closed, if sprint is closed, this table should be empty
    FILTER(
        'Table',
        'Table'[Sprint] = _CurrentSprint && 'Table'[Status] <> "Closed"
    )
VAR _Result =                               //checking if there are no rows in OpenUserStories. If it's empty, then all items are closed
    IF(
        COUNTROWS( _OpenUserStories ) > 0,
        "Active",
        "Closed"
    )

RETURN _Result

 
Once you have it, you can proceed with assigning actual story points:

Actual Story Points = IF( 'Table'[Sprint Status] = "Closed", 'Table'[Story Points], 0 )

 
Finally you calculate the average of closed stories:

Average Story Points = 
VAR _NumberOfClosedStories =                        //filter the table only to closed sprints, summarize it by distinct sprints and count them
    COUNTROWS(
        SUMMARIZE(
            FILTER(
                'Table',
                'Table'[Sprint Status] = "Closed"
            ),
            'Table'[Sprint]
        )
    )
VAR _ActualStoryPointsToConsider =                  //number of actual points to consider
    SUMX(
       FILTER(
            'Table',
            'Table'[Sprint Status] = "Closed"
        ), 
        [Actual Story Points]
    )
VAR _Result =                                       //get the average dividing points by number of closed stories
    DIVIDE( 
        _ActualStoryPointsToConsider, 
        _NumberOfClosedStories 
    )

RETURN _Result

Here is the final result:

Take your time to read carefully the code with the corresponding comments. I'm attaching pbix to make it easier for you. 

If you have difficultires to understand why the code is written in a specific way, make sure to master concepts of "filter context" (Filter context in DAX - SQLBI) and "row context" (Row context in DAX - SQLBI). These are keys to undertand the code. 

Good luck with your project!

[Sergii24 · Accepted solution]

Hi @amit_wairkar, so what exactly is your question? What have you tried to do? What is not working? What do you struggle with (hint: everything is not an anwer 😉 ).

Remeber that community is here to help to those who wants to learn, not solving task for you 🙂