DAX query for flattened data from JIRA

[xmark]

hi all,

I am trying to extract data from my JIRA instance (via CSV) and build a report on points "completed" and "committed" per sprint.

When the CSV file exports, each Story has some flattened columns, Sprint, Sprint_12, Sprint_13 which appear to hold a value on which sprint each story started on (the "Sprint" column), and if the story moved over to the next sprint, then the value in Sprint_12 would contain the name of the next sprint and so on.

For example, Story ABC123 starts in "Sprint 1", but takes longer than a sprint to complete, so it moves into the next sprint, so the values for the Sprint column would be "Sprint 1" and Sprint_12 would be "Sprint 2".

If this story was worth 8 points, and was completed in Sprint 2. I want a report that shows a clustered column chart, showing 8 points "committed" in sprint 1, but zero points completed, and 8 points "committed" in Sprint 2, with 8 points completed.

I dont know how to construct the clustered column chart so that "some" axis field is used as the Sprint field (which one?) and then I want 2 measures for completed points for that sprint, and committed points for that sprint... 

It sounds easy, so I hope I am missing something small, but I can only get the "completed" numbers correct, but I dont know how to account for a story that was committed over 2 sprints.

[v-yulgu-msft]

Hi @xmark ,

Please eloborate your scenario with dummy data and desired output.

How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[xmark]

Hi @v-yulgu-msft thanks ill try again to show some sample data and desired output.

Its only 1 table -called "JIRA" - and the relevant columns are:

StoryNumber, Sprint,Sprint_12,Sprint_13,Status, Story Points

StoryNumber is the unique key of the user story

Sprint is always the text field name of the sprint where the story started

IF Sprint_12 is populated, that means that the story started in the "Sprint" sprint, but was shifted into Sprint_12

IF Sprint_13 is populated, that means that the story started in the "Sprint" sprint, but was shifted into Sprint_12, THEN moved into Sprint_13

Status is the story status - I am only interested in "Done" stories

Story Points - is the allocated points that I want to total about the stories (think agile story points)

Here is some sample data:

StoryNumber	Sprint	Sprint_12	Sprint_13	Status	Story Points
SN123	First Sprint			Done	4
SN222	First Sprint	Second Sprint		Done	8
SN555	Second Sprint			Done	2
SN678	Second Sprint	Third Sprint		Done	16
SN765	Third Sprint	Fourth Sprint		Done	4
SN888	Third Sprint	Fourth Sprint	Fifth Sprint	Done	8

My desired output would be a report per sprint (5 in the case above) - that really has 2 measures:

1. Committed - This is the SUM of the Story Points of stories that were STARTED in each sprint
2. Completed - This is the SUM of the Story Points of stories that were Done in each sprint

SPRINT	Committed	Completed
First Sprint	12	4
Second Sprint	18	10
Third Sprint	12	20
Fourth Sprint	0	0
Fifth Sprint	0	8

I hope this makes sense!