Timeline of accumulated due Storypoints with filtering multiple custom fields

[canedha]

Hi,

I have a couple of Jira tickets assigned to a Name and a Stage by labels. Each ticket has a unique combination of Name and Stage. Both of them are extracted as customfields by JS and imported as dimension and property. Storypoints is imported as measure and porperty as well.

With the imported fields:

“Name_Val” (customfield string),
“Stage_Val” (customfield integer),
“Due date” (normal jira field),
“Story Points” (normal jira field)

I can get a table like this:

Name	Stage	Due date	StoryPoints	Sum due
A	1	01.01	20	20
A	1	01.06	40	60
A	1	01.09	40	100
A	2	01.10	30	30
A	2	01.12	70	100
B	1	01.01	45	45
B	1	01.07	55	100

The % Due ([Measure].[Sum Due]) are calculated by:

NonZero(
  Sum(
  [Issue.Epic].[Parent].GetMemberbyKey([Issue].CurrentHierarchyMember.Key),
  Sum(
    Filter(
      [Issue].[Issue].Members,
      DateCompare(
      [Issue].CurrentMember.get('Due date'),
      [Issue.Epic].[Parent].CurrentMember.Get('Due date')
      ) <= 0
      AND
      [Issue].CurrentMember.Get('Name_Val') MATCHES [Issue.Epic].[Parent].CurrentMember.Get('Name_Val')
      AND
      [Issue].CurrentMember.GetString('Stage_Val') MATCHES [Issue.Epic].[Parent].CurrentMember.GetString('Stage_Val')
      
        ),
      [Issue].CurrentMember.Get('Story Points (Portfolio)')
      )
    )
  )

The goal is to show a line graph/timeline consisting of a series of lines depicting the Sum Due for each combination of Name and Stage over Time. In my upper exapmple it would show 3 lines A1, A2 & B1.
Unfortunately with [Measure].[Sum Due] the Time/Due date gets lost.

Next step would be to not show the Sum due but the percentage (= [Measure].[Sum Due]/[Measure].[Sum Due total])

I thought maybe I’m completely on the wrong track and have to use something I found in the templates like:

CatchException(
  CASE WHEN [Measures].[Story Points (Portfolio) with due date] = 0 THEN 0
  WHEN NOT IsEmpty([Measures].[Story Points (Portfolio) with due date])
  THEN
    [Measures].[Story Points (Portfolio) with due date] /
    ConstantColumnValue(Sum(TotalRowsSet(), [Measures].[Story Points (Portfolio) with due date]))
  END
)

But in this case I cannot filter for Name and Stage combinations. I saw some answers proposing to create a JS to assign a combination A1 etc with Import already but the number of combinations is just to big and therefore needs to be dynamic.

Any help is highly appreciated!

[zane.baranovska]

Hi @canedha,

I woudl suggest another approach to the report using standard functionality without calcauted measures.

1. Import “Stage_Val” as a dimension. See documentation on Custom field import options

2. In the report, set the “Time” dimension on report rows.

3. On columns, select the measure “Story Points with due date” to show how many story points are due each period.

4. In the report, click on the “Story Points with due date” header and add standard calcaution: Add calculated → Cumulative sum → including empty. See documentation for more details: Add standard calculations.
   When calculation is added, remove “Story Points with due date” leaving only accumalted values.

5. Now add dimensions “Name_Val” and “Stage_Val” on the report columns to see accumulated values for each valid combinations. Chose to hide empty columns
   The report in table view should look like in the picture below.
   

   

6. Now you can switch to the best representation for your report.
   To show continuous lines for each option choose Timeline chart.
   

   

   Or a Bar chart for showing the percentage of each combination. Customize the chart for better data representation (see also documentation for more detailed instructions: Customize chart):

   • chose chart representation Vertical
   • for each value combination, change representation type → column
   • for each value combination, change stacked → 1 to force them be stacked in one column.
   • chose chart representation Percentage
   

Best regards,
Zane / support@eazyBI.com