Count of carried Keys (Stories) from one sprint to another sprint

[Anonymous]

Hi,

I have an requirement for count  of carried Key from one sprint to another sprint..

Please see the below data regarding requirement 

I have tried with some dax formulas unfortunatley I couldn't able to find the correct output.. Below is the wrong output which I am getting now...

Correct output should be

 For GV sprint 1carried Keys should be "0" ( because there is no Keys carried from previous sprint)

For GV sprint 2 carried Keys should be "5" ( Which are carried from previous sprint (GV Sprint 1))

 For GV Sprint 3 Carried Keys should be "Actual Keys which are Carried from GV Sprint 2"

Like wise All the GV sprints should show the count of carried stories from previous sprints. 

Thanks

Vijay 

[Anonymous]

1. There is no separate column for sprint number. We can consider Sprint name as sprint number because in that column we can see the sprint number as well...

2. There is no Index column

Thank you...

[tamerj1]

@Anonymous 

You need an integer data type column otherwise we cannot recognize which Sprint name is the next one. Are you allowed to add columns, probably easiest using power query. Just get the number in a seperate whole number type column. 

[Anonymous]

I am working end to end in this project .. I am allowed to create columns

Please tell me what need to be done exactly to get correct out put...

[tamerj1]

@Anonymous 

Open the query editor and select add column "From Example". Type the column name then start inserting manualy the number of the sprint. After 2 or 3 examples power query will be to identify the patern and will automatically insert all other values. Once done please let me know in order to guide you through the complete solution. 

[Anonymous]

I have Inserted the column as you said... Please tell me the next step.

[tamerj1]

@Anonymous 

If sprint numbers are not continuous series then I hope the following works

Currid Keys =
VAR CurrentSprintNumber =
    MAX ( Table[Sprint_Number] )
VAR CurrentSprintKeys =
    VALUES ( Table[Key] )
VAR PreviousSprintNumber =
    MAXX (
        FILTER (
            ALL ( Table[Sprint_Number] ),
            Table[Sprint_Number] < CurrentSprintNumber
        ),
        Table[Sprint_Number]
    )
VAR PreviousSprintKeys =
    CALCULATETABLE (
        VALUES ( Table[Key] ),
        Table[Sprint_Number] = PreviousSprintNumber
    )
RETURN
    COUNTROWS ( INTERSECT ( CurrentSprintKeys, PreviousSprintKeys ) )

[tamerj1 · Accepted solution]

@Anonymous 

After changing the data type to whole number and hopping that the sprint numbers are continuous series (please let me know if otherwise), then you can create the following measure

Currid Keys =
VAR CurrentSprintNumber =
    MAX ( Table[Sprint_Number] )
VAR CurrentSprintKeys =
    VALUES ( Table[Key] )
VAR PreviousSprintKeys =
    CALCULATETABLE (
        VALUES ( Table[Key] ),
        Table[Sprint_Number] = CurrentSprintNumber - 1
    )
RETURN
    COUNTROWS ( INTERSECT ( CurrentSprintKeys, PreviousSprintKeys ) )

[tamerj1]

@Anonymous 

Also please advise how are you planning to disply the results

[Anonymous]

I have changed this data type to whole number..

I have to show this data in column chart as per Sprints ..( Example :- In Xaxis,  I will give Sprint names & In Values, I will give Carried Keys Count )

[tamerj1]

Change data type to whole number

[tamerj1]

@Anonymous 

Do you have a sprint number or index column in your source data?