Custom field value report with specific state transition start date

Questions & Answers
1

Hi,

I would like to create a report similar to below, where report will summarize total of Time consumed custom field value based on assignee and component. However, when import “Time consumed” custom field as either property or measure, the predefined custom measures only limited as shown in image below. I would like report to have summarize total Time consumed based on first day issue entered “In Progress” state rather than when issue is created. Is there anywhere to perform this?

measure
measure822×380 13.7 KB

report
report1060×381 51.9 KB

Thanks,
HG

2

Hi @HgNeoh ,
If you have the field in Jira as number type, then select in eazyBI account import setting to import this field value changes as well:

image
image1494×152 15.4 KB

It will create a measure “Time consumed history” that you can use in your calculations.
Based on this community post you can create a similar formula to get the historical data from your field on a particular day when the issue transitioned to another status:

And if you want to sum the result, then you can create this formula:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      ([Measures].[Transition to status first date],
      [Transition Status].[In Progress],
      [Time].CurrentHierarchy.DefaultMember)),
      [Time].CurrentHierarchyMember
    )
  ),
  (
  [Measures].[Time consumed history],
  [Time].[Day].DateMember(
   ([Measures].[Transition to status first date],
    [Transition Status].[In Progress],
    [Time].CurrentHierarchy.DefaultMember))
  )
)

Keep in mind, that the “Assignee” dimension is linked to historical data, thus it means that the results will be summed by assignees who were on the date when the issue transition happened.

best,
Gerda // support@eazyBI.com

3

Thank you so much for your reply. Will check and update it here so that other users can refer to this post.

1 Like
4

I have tried and it does not work as the “Time consumed change” measure would return negative values. Btw, this “Time consumed” field will only get its first value once issue is moved to In Progress. Then the value will be auto incremented daily as long as it is still stay in “In Progress”. Is this the reason negative values being computed by the logic? Appreciate if more pointers can be given as I am new to eazyBI.

5

Hi @HgNeoh,
You should use the measure “Time consumed history” and not the measure “Time consumed change”.
Secondly, the calculation I shared gets the field “Time consumed” value which is on the date the issue is moved to “In Progress” and ignores any changes after.
If you want to see how the field changes value over time, then use the measure “Time consumed history” together with the “Time” dimension.
Here is an example of how it looks with my test data:

eazyBI_number_field_changes_over_time
eazyBI_number_field_changes_over_time2812×714 299 KB

If this doesn’t work, please share more information about your use case and data directly with the eazyBI team: support@eazyBI.com

best,
Gerda // support@eazyBI.com

6

Hi Gerda,

Thanks so much for the prompt response. Strangely, both “Time consumed history” and “Time consumed change” returned same set of values (negative values). I have dropped an email to support@eazyBI.com. Thanks for all the helps!

7

Talked with @HgNeoh via eazyBI support channel and we clarified the use case. Idea is to get the current value of the issue field “Time consumed” and link it to the Time dimension by the date when the issues transitioned to the “In progress” status.

Here is the formula:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      ([Measures].[Transition to status first date],
      [Transition Status].[In Progress],
      [Time].CurrentHierarchy.DefaultMember,
      [Assignee].CurrentHierarchy.DefaultMember),
      [Time].CurrentHierarchyMember
    )
  ),
  ([Measures].[Time consumed created],
  [Time].CurrentHierarchy.DefaultMember)
)

best,
Gerda

8

Thank you so much @gerda.grantina for your prompt support on this issue!

1 Like