Custom field value report with specific state transition start date

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?

Thanks,
HG

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:

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

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

1 Like

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.

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:

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

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!