topic Re: Count of carried Keys (Stories) from one sprint to another sprint in DAX Commands and Tips

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

Anonymous — Mon, 18 Apr 2022 15:55:22 GMT

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

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

tamerj1 — Mon, 18 Apr 2022 16:23:15 GMT

Anonymous

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

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

Anonymous — Mon, 18 Apr 2022 16:44:08 GMT

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...

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

tamerj1 — Mon, 18 Apr 2022 16:54:38 GMT

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.

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

Anonymous — Mon, 18 Apr 2022 17:07:14 GMT

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...

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

tamerj1 — Mon, 18 Apr 2022 17:20:33 GMT

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.

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

Anonymous — Mon, 18 Apr 2022 17:44:36 GMT

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

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

tamerj1 — Mon, 18 Apr 2022 17:51:39 GMT

Change data type to whole number

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

tamerj1 — Mon, 18 Apr 2022 17:54:16 GMT

Anonymous

Also please advise how are you planning to disply the results

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

tamerj1 — Mon, 18 Apr 2022 18:05:26 GMT

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 ) )

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

Anonymous — Mon, 18 Apr 2022 18:05:53 GMT

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 )

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

tamerj1 — Mon, 18 Apr 2022 18:17:13 GMT

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 ) )