Hi Eazybi. We are running program increments and at the end of a quarter we would like to see how many epics completed in that quarter and how many missed. I would like to create a chart where we see last 4 quarters how we did committed vs completed as we do for sprints for instance. The Epics will have a measure “issue target end date” data will hold the date when we want that epic to be completed. Can we do something with this date to determine it is was done in the quarter or not?
Maybe this straightforward solution makes sense for you. Because you have to measure somehow the “work done”. And more for a date field, it has to be managed by Status = Closed
So, in the rows you only need to have this:
and in Rows:
Hi @Nacho,
You might first create a calculated member in the Time dimension for the last 4 quarters.
You might get some inspiration here - Chart Types - Jira Demo - eazyBI.
The expression for the “last 4 completed quarters” might be as follows.
Aggregate(
[Time].[Quarter].DateMembersBetween(
-- 5 quarters back to include 4 full completed quarters
DateAdd("q",-5,now()),
-- previous quarter
DateAdd("q",-1,now())
)
)
Further calculations might require additional clarifications:
- What do you mean by completed epic? Is it the resolution of Epic or its children, or some status transition?
- What do you consider a miss? Completion earlier/later? Not completed until now?
The size of your instance also impacts the efficiency of calculations. Smaller instances would do just fine with iterative calculations. However, larger instances might greatly benefit from JavaScript precalculated fields.
The simple expression that counts the Epics that have been committed to the current quarter and have been resolved within that quarter might look as follows.
CASE WHEN
-- there are committed epics in the quarter - iteration relevant
([Measures].[Issues with Target end date],
[Issue Type].[Epic])>0
THEN
Sum(
Filter(
-- set of epics
DescendantsSet(
[Issue.Epic].CurrentMember,
[Issue.Epic].[Epic]),
-- conditions for epics
-- committed to quarter
DateInPeriod(
[Issue].CurrentHierarchyMember.Get('Target end date'),
[Time].CurrentHierarchyMember)
AND
DateInPeriod(
[Issue].CurrentHierarchyMember.Get('Resolved at'),
[Time].CurrentHierarchyMember)
),
-- numeric value for sum - executed on filtered issues
-- genuine measure ensures relevance to the report context
([Measures].[Issues with due date],
-- only check the epic relevance
[Issue Type].[Epic])
)
END
The expression to find the “Missed epics” that have not been delivered until the end of their committed quarter might be as follows.
CASE WHEN
-- there are committed epics in the quarter - iteration relevant
([Measures].[Issues with Target end date],
[Issue Type].[Epic])>0
THEN
Sum(
Filter(
-- set of epics
DescendantsSet(
[Issue.Epic].CurrentMember,
[Issue.Epic].[Epic]),
-- conditions for epics
-- committed to quarter
DateInPeriod(
[Issue].CurrentHierarchyMember.Get('Target end date'),
[Time].CurrentHierarchyMember)
AND
-- not delivered
NOT DateBeforePeriodEnd(
[Issue].CurrentHierarchyMember.Get('Resolved at'),
[Time].CurrentHierarchyMember)
),
-- numeric value for sum - executed on filtered issues
-- genune measure ensures relevance to the report context
([Measures].[Issues with due date],
-- only check the epic relevance
[Issue Type].[Epic])
)
END
Regards,
Oskars / support@eazyBI.com
edited - adjusted the custom date field name
They thank you for replying. At the surface the ask is simple, how many epics we completed in the planned timeline. Well this becames a bit difficult because:
- first we need to identify epics that have been commited to which quarter. In my case I either use a custom date “target end” which is actually a date or I use custom filed, planned quarter which is somehting like “CY25Q2”;
- then I need to identify if the epics have been completed in the specific quarter. Here I have a specific status they need to get to which is “Ready for Validation”.
In the end I would like to show to leaders
Q1 planned 10 epics closed 4
Q2 planned 6 epics CLosed 8
etc
I hope this helps and I hope I can find a solution as is a basic ask that eazybi tools needs to solve
Thank you for the update.
Consistent data allows the creation of clear and efficient reports. And the report can only be as good as the source data.
The fact that the epic relation to a quarter is defined in either a customfield with custom periods or a date field might often lead to mismatched values.
There are details that might change the calculation:
- Which value prevails if the same epic has both “planned quarter” and “target end” in a different quarter?
- Are those fields static in the sense of being defined once and not changed even after epic misses the quarter, or could the same epic be committed to several quarters?
- If they are adjusted along the way, are they consistently changed before the start of the quarter, after the end of the missed quarter, or anytime?
- Is “Ready for Validation” the final status for the Epic?
- Could the same epic get through that status more than once (if/after being rejected)?
- If the same epic could get through that status several times, which time should be counted - first, last, or each?
Different policies lead to different calculation methods.
Please see below the calculation that might show the delivered epics.
Sum(
Filter(
DescendantsSet(
[Issue].CurrentHierarchyMember,
[Issue].[Issue]),
-- filter conditions for issues
[Measures].[Issue type] = "Epic"
AND
(
-- committed to quarter by date
DateInPeriod(
[Issue].CurrentHierarchyMember.Get('Target end'),
[Time].CurrentHierarchyMember)
OR
-- committed to quarter by customfield
[Issue].CurrentHierarchyMember.Get('Planned Quarter')
=
"CY"||
Right(Ancestor(
[Time].CurrentHierarchyMember,
[Time].[Year]).Name,2)
||
Left(Ancestor(
[Time].CurrentHierarchyMember,
[Time].[Quarter]).Name,2)
)
),
-- numeric value for sum - executed on committed epics
IIF(
DateInPeriod(
([Measures].[Transition to status last date],
[Transition Status].[Ready for Validation],
-- resetting Time contect to see the ultimate transition date
[Time].CurrentHierarchy.DefaultMember),
-- last transition was in displayed period
[Time].CurrentHierarchyMember),
-- include this epic
1,
-- exclude this epic
0)
)
The calculation is based on the following assumptions:
- The “Planned Quarter” is imported as issue property, the periods exactly match the calendar periods with naming convention “CYaaQb” where “aa” stands for the calendar year last two digits and “b” stands for the number of calendar quarter
- The epic is only committed once, and the value is never changed
- The Epic is counted towards any match of the Planned Quarter or Due date, and could appear as committed to several quarters if the values mismatch
- The Epic might go through “Ready for Validation” several times, and only the last date counts
- The report displays the quarter level of the Time dimension default hierarchy on the report rows
If these assumptions are wrong, please write to us directly with a detailed description of your workflow, planned quarter naming convention, and the required calculation logic.
While some calculations on the consistent dataset could be simple and executed within milliseconds, more complex calculations that have to consider alternatives might require either slow and complex iterative calculations or the creation of JavaScript calculated fields that move complexity from report execution to data import.
As we say - “simple things easy, complex things possible”
Regards,
Oskars /support@eazyBI.com
Hey Oskars, I appreciate your help on this. I would rather keep the conversation here so the community can benefit as well. I find the planned vs commited at the quarter level to be quite basic report. And while eazybi is powerful and really flexible sometime it can be hard to create custom metrics on my own >> so once again super appreciate your effort.
I was traveling and hence the late response but I am hugely interested in this metric.
A few things:
- yes the naming convention will always be CY25Q3 for year and quarter after the “CY”
- The planned quarter is not static, it can change. AT this point we have no interest in keeping track of histoty. So yes it might appear not delievered and then delivered if we change the value to match the delivered date
- Ready for Validation is not the last status and can go multiple times and yes last transition si what we care for.
- Or beter yet I am thinking we can have the first closed/resolved status with a grace of 15 days period. Because what happens to move things to production usually can take a week or two. So maybe this can be an alternative to Ready for Validation Specific status.
I am going to test you report and come back. Once again gracias for the help, we were blocked here.
This is what I got, I am still looking at the results. Can you update the custom metric to account for my updates above?
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “PI Delivery Performance”,
“folder_name”: “Program Increment”,
“result_view”: “bar_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Issues created]”,“[Measures].[Epics Delivered In Quarter]”],“members”:}]},“rows”:{“dimensions”:[{“name”:“Issue”,“selected_set”:[“[Issue].[All Issues]”],“members”:,“bookmarked_members”:},{“name”:“Time”,“selected_set”:[“[Time].[All Times]”],“selected_set_expression”:“DescendantsSet({{selected_set}}, [Time].[Quarter])”,“members”:,“bookmarked_members”:}],“filter_by”:{“conditions”:[{“expression”:“[Time].CurrentHierarchyMember”,“operator”:“between”,“value”:“12 months ago and today”,“value_type”:“date”}]},“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:[“[Project].[All Projects]”],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:“[Project].[All Projects]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“TrialMax”,“full_name”:“[Project].[TrialMax]”,“drillable”:true,“key”:“TMAX”,“parent_full_name”:“[Project].[All Projects]”}],“bookmarked_members”:,“current_page_members”:[“[Project].[TrialMax]”]},{“name”:“Program Increment”,“selected_set”:[“[Program Increment].[All Program Increments]”],“members”:[{“depth”:0,“name”:“All Program Increments”,“full_name”:“[Program Increment].[All Program Increments]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“CY25Q2”,“full_name”:“[Program Increment].[CY25Q2]”,“parent_full_name”:“[Program Increment].[All Program Increments]”},{“depth”:1,“name”:“CY25Q3”,“full_name”:“[Program Increment].[CY25Q3]”,“parent_full_name”:“[Program Increment].[All Program Increments]”},{“depth”:1,“name”:“CY25Q4”,“full_name”:“[Program Increment].[CY25Q4]”,“parent_full_name”:“[Program Increment].[All Program Increments]”}],“bookmarked_members”:,“current_page_members”:[“[Program Increment].[CY25Q2]”,“[Program Increment].[CY25Q3]”,“[Program Increment].[CY25Q4]”]},{“name”:“Issue Type”,“selected_set”:[“[Issue Type].[Issue Type].Members”],“members”:[{“depth”:1,“name”:“Epic”,“full_name”:“[Issue Type].[Epic]”}],“bookmarked_members”:,“current_page_members”:[“[Issue Type].[Epic]”]},{“name”:“Time”,“duplicate”:true,“selected_set”:[“[Time].[All Times]”],“members”:[{“depth”:0,“name”:“All Times”,“full_name”:“[Time].[All Times]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:,“current_page_members”:[“[Time].[All Times]”]}]},“options”:{},“view”:{“current”:“bar_chart”,“maximized”:false,“bar_chart”:{“stacked”:false,“vertical”:true,“swap_axes”:false,“data_labels”:false,“series_options”:{“Issues created”:{“dataLabelType”:“top horizontal”},“Epics Delivered In Quarter”:{“dataLabelType”:“top horizontal”}}},“table”:{}},“calculated_members”:}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Transition to status last date”,“format_string”:“yyyy-mm-dd hh:nn:ss”,“formula”:“TimestampToDate(\n [Measures].[Transition to status last timestamp]\n)”},{“dimension”:“Measures”,“name”:“Issue type”,“format_string”:“”,“formula”:“[Issue Type].[Issue Type].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Issue type ID’)\n)”},{“name”:“Epics Delivered In Quarter”,“dimension”:“Measures”,“formula”:“Sum(\n Filter(\n DescendantsSet(\n [Issue].CurrentHierarchyMember,\n [Issue].[Issue]),\n– filter conditions for issues\n [Measures].[Issue type] = "Epic"\n AND\n (\n– committed to quarter by date\n DateInPeriod(\n [Issue].CurrentHierarchyMember.Get(‘Target end’),\n [Time].CurrentHierarchyMember)\n OR\n– committed to quarter by customfield\n [Issue].CurrentHierarchyMember.Get(‘Planned Quarter’)\n =\n "CY"||\n Right(Ancestor(\n [Time].CurrentHierarchyMember,\n [Time].[Year]).Name,2)\n ||\n Left(Ancestor(\n [Time].CurrentHierarchyMember,\n [Time].[Quarter]).Name,2)\n ) \n ),\n– numeric value for sum - executed on committed epics\n IIF(\n DateInPeriod(\n ([Measures].[Transition to status last date],\n [Transition Status].[Ready for Validation],\n– resetting Time contect to see the ultimate transition date\n [Time].CurrentHierarchy.DefaultMember),\n– last transition was in displayed period\n [Time].CurrentHierarchyMember),\n– include this epic\n 1,\n– exclude this epic\n 0)\n)”,“format_string”:“#,##0.00”}]
}