Hi EazyBI teams,
I have a custom requirement for Epic cycles & Lead Times.
Epic Cycle Times: Since we do not want to be dependent on the Epic statuses, we want a new way which will be automatic. i…e We want the Epic cycle time to be started as soon as the first story within the epic moves into Development. The Epic cycle time should also end when the last story in the epic is Done.
To further simplify: The epic cycle time is calculated by epic resolution date minus the 1st relevant story moved into Progress. If the epic does not have any stories in it then the Cycle time would be calculated number of days the epic was in progress.
The story Cycle time is the amount of days the story was in Progress & in Blocked status. (For blocked we use Jira’s default feature Flagged to indicate that it is blocked). The story can be flagged/blocked in any status.
So basically, we need a table where the Rows would be different projects & Columns would be the Epic cycle time & Story cycle time. Additional columns 80th Percentile, Avg Cycle Time & Median Cycle time for both Stories & Epics.
Additionally, the report should have filter to select the “fix version” so that we can see the cycle times for a release. & additionally, a filter to further filter the epics which were created during the release only & one last filter which can help me view Cycle times for In Progress Status epics.
Requesting your help in building this report. Thanking in Advance.
The filters are not present in the above image but are required.
Here is an example formula to calculate the Epic cycle time based on the date when either story or epic moved to In progress. The formula includes a filter by epic created date based on the selected version.
NonZero(Avg(
Filter(
Descendants([Issue.Epic].CurrentMember, [Issue.Epic].[Epic]),
-- show time for resovled epics only
DateInPeriod(
[Issue].CurrentHierarchyMember.Get('Resolved at'),
[Time].CurrentHierarchyMember
)
AND
-- filter epics created during the fix version if one version selected in report
IIF(not IsEmpty([Measures].[Version start date]),
DateBetween(
[Measures].[Issue created date],
[Measures].[Version start date],
[Measures].[Version release date]), 1)
),
DateDiffDays(
-- get either Story first transition to in progress or from epcis if there is no stories
TimestampToDate(CoalesceEmpty(
DateToTimestamp(
([Measures].[Transition to status first date],
[Transition Status.Category].[In Progress],
[Issue Type].[Story],
[Time].CurrentHierarchyMember)),
DateToTimestamp(
([Measures].[Transition to status first date],
[Transition Status.Category].[In Progress],
[Issue Type].[Epic],
[Time].CurrentHierarchyMember))
)) ,
-- epic resolution date
[Issue].CurrentHierarchyMember.Get('Resolved at')
)
))
The formula calculates Avg, you can use the same formula to calculate Median and Percentile. Swtich the Avg to the needed function. Please add an additional parameter 80 for Percentile to get the needed percentile value.
Daina / support@eazybi.com
Hello Daina,
Thanks a lot for reverting so quickly. Appreciate it!
However, I would need further help in replicating the table mockup I sent in the original request. I created a calculated measure using the above formula but I don’t think it is giving me correct data. Below is the screenshot for your reference.
for example: The last row has 1 Epic closed which is right but the cycle time that I see from the history of the epic was moved to development on 12 Oct & was moved to done on 20 Oct… There were approximately 10 stories under it & if we look at their time then it must definitely be more than the epic time. So i guess something in the formula or maybe something the way I created the report is not ok.
Please suggest!
The formula uses the function DateDiffDays and the result is in days. You are using the minutes formatter in the report. It requires results in minutes for a correct representation. You would like to switch the function DateDiffDays to DateDiffMinutes for this.
Any formula might work differently based on the report setup. It also might impact some results. I made some small changes to the formula to make it work more universally as well.
Here is an updated formula using both suggestions:
NonZero(Avg(
Filter(
Descendants([Issue.Epic].CurrentMember, [Issue.Epic].[Epic]),
-- show time for resovled epics only
DateInPeriod(
[Issue.Epic].CurrentMember.Get('Resolved at'),
[Time].CurrentHierarchyMember
)
AND
-- filter epics created during the fix version if one version selected in report
IIF(not IsEmpty([Measures].[Version start date]),
DateBetween(
[Measures].[Issue created date],
[Measures].[Version start date],
[Measures].[Version release date]), 1)
),
CASE WHEN ([Measures].[Issues resolved], [Issue Type].[Epic]) > 0
THEN
DateDiffMinutes(
-- get either Story first transition to in progress or from epcis if there is no stories
TimestampToDate(CoalesceEmpty(
DateToTimestamp(
DefaultContext(
([Measures].[Transition to status first date],
[Transition Status.Category].[In Progress],
[Issue Type].[Story],
[Issue.Epic].CurrentMember,
[Time].CurrentHierarchyMember))),
DateToTimestamp(
DefaultContext(
([Measures].[Transition to status first date],
[Transition Status.Category].[In Progress],
[Issue Type].[Epic],
[Issue.Epic].CurrentMember,
[Time].CurrentHierarchyMember)))
)) ,
-- epic resolution date
[Issue.Epic].CurrentMember.Get('Resolved at')
)
END
))
Daina / support@eazybi.com
Hello @daina.tupule
Thanks a lot for the updated formula, it works. The values are pretty close to what I am expecting. I was able to get the Median & the 80th Percentile also working.
However, can you also suggest a formula to calculate the cycle times of Epics that are in progress also? So basically, I would like to know what is the cycle time of all the epics which are currently in progress. Ideally, that formula should work with the Issue dimension also, i.e. would like to see cycle times for individual epics which are in progress when I use the issue dimension.
Could you check this report example to see if you can use those example formulas to get the time in current Epic status.
Daina / support@eazybi.com
Hello @daina.tupule ,
The report that you provided uses the normal epic statuses however as we are discussing on this thread, I need the same logic applied here also.
Need formula to calculate the cycle times of Epics that are in progress also? (based on logic that if a story within the epic is moved to in progress, the cycle time of the epic should start & if epics does not have any stories within it, then we can use the epic statuses for cycle time calculation.
Ideally, that formula should work with the Issue dimension also, i.e. would like to see cycle times for individual epics which are in progress when I use the issue dimension.
Please suggest!
Do I understand you correctly? You would like to update the initial formula and include not only resolved epics but currently unresolved epics as well. You would like to calculate the current cycle time for unresolved epics.
I removed the filter by resolved epics. I included the additional condition to pick the current time (Now) if the epic is not resolved.
NonZero(Avg(
Filter(
Descendants([Issue.Epic].CurrentMember, [Issue.Epic].[Epic]),
-- filter epics created during the fix version if one version selected in report
IIF(not IsEmpty([Measures].[Version start date]),
DateBetween(
[Measures].[Issue created date],
[Measures].[Version start date],
[Measures].[Version release date]), 1)
),
CASE WHEN ([Measures].[Issues created], [Issue Type].[Epic]) > 0
THEN
DateDiffMinutes(
-- get either Story first transition to in progress or from epcis if there is no stories
TimestampToDate(CoalesceEmpty(
DateToTimestamp(
DefaultContext(
([Measures].[Transition to status first date],
[Transition Status.Category].[In Progress],
[Issue Type].[Story],
[Issue.Epic].CurrentMember,
[Time].CurrentHierarchyMember))),
DateToTimestamp(
DefaultContext(
([Measures].[Transition to status first date],
[Transition Status.Category].[In Progress],
[Issue Type].[Epic],
[Issue.Epic].CurrentMember,
[Time].CurrentHierarchyMember)))
)) ,
-- epic resolution date or today for unresolved epics
TimestampToDate(
CoalesceEmpty(
DateToTimestamp([Issue.Epic].CurrentMember.GetDate('Resolved at')),
DateToTimestamp(Now())
)))
END
))
Daina / support@eazybi.com
1 Like
