Hi Roman,
Time since first to Status ‘In Progress’ to last transition to 'Closed’
For the first calculation, you can use historical measure Transition to status first date to set a date when issue moved to status In Progress. You should use the measure on Issue level only to get correct results. I also would suggest using this measure in a tuple with Time and Sprint default members to get this date for the first time issue moved to this status no matter when and in which Sprint the issue was during this status transition.
You can use default imported property Issue closed date. We create this date based on specified closing statuses in import settings. eazyBI calculates the last time when issue transits to any of closed statuses.
Here is an example formula for this calculation:
NonZero(AVG(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- filter by some date property, typically for closed issues when cycle is completed
not IsEmpty([Issue].CurrentHierarchyMember.get('Closed at'))
AND
DateInPeriod(
[Issue].CurrentHierarchyMember.get('Closed at'),
[Time].CurrentHierarchyMember
)
AND
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0
)
,
-- date difference between moving in progress to closing
DateDiffDays(
([Measures].[Transition to status first date],
[Transition Status].[In Progress],
[Time].CurrentHierarchy.DefaultMember,
[Sprint].Defaultmember),
[Issue].CurrentHierarchyMember.get('Closed at')
)
)
)
Time since first set the story-point or an estimation-time to Status 'Done’
eazyBI does not import date when estimates for an issue is set. You can create a JavaScript calculated custom field and find this date based on issue history. You can import the date as property and then use in the similar calculation as above.
However, I would suggest importing already calculated time in days between the date when an estimate is set for an issue and issue resolution date (typically set when an issue moves to Done status).
Here is an example custom field definition with JavaScript code for calculating days from issue estimation date untill resolution date for resolved issues:
[jira.customfield_timefromestimate]
name = "Time from estimate"
data_type = "decimal"
measure = true
javascript_code = '''
var estimatedate = null;
// for resolved issues only
if (issue.fields.resolutiondate){
label:
if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
var histories = issue.changelog.histories;
for (var i = 0; i < histories.length; i++) {
var history = histories[i];
if (history.items && history.items.length > 0) {
for (var n = 0; n < history.items.length; n++) {
var item = history.items[n];
if ( item.field == 'Story Points' || item.field == 'timeestimate' ) {
if ( item.fromString == null && item.toString && item.to != 0 ) {
estimatedate = history.created;
break label;
}
} } } } }
if(estimatedate) {
issue.fields.customfield_timefromestimate = (Date.parse(issue.fields.resolutiondate) - Date.parse(estimatedate)) / 1000 / 60 / 60 / 24;;
}
}
'''
Add the custom field definition to eazyBI advanced settings or ask JIRA administrator to do this for you, as only JIRA administrators, has access to eazyBI advanced settings. eazyBI advanced settings are global for Jira instance.
Then select the new custom field Time from estimate for import as measure and property for each account you would need this calculation and run an import.
eazyBI will create several measures including Time from estimate resolved and hidden measure Issues with Time from estimate resolved. You can use those measures to calculate an average time in issues from estimation till resolution:
CASE WHEN
[Measures].[Time from estimate resolved] > 0
THEN
[Measures].[Time from estimate resolved]
/
[Measures].[Issues with Time from estimate resolved]
END
Both formulas will work for the last/current Sprint of an issue.
Daina / support@eazybi.com
