I have a unique requirement for which we are looking for a solution in EazyBI for Jira. Let me explain with example mentioned below:
I have Issue A and Issue B.
Issue A was added to Sprint 1 and Issue B was added to Sprint 2.
After sprint 1 completed, Issue A was moved to Sprint 2, since issue was incomplete
I want to create a tabular view where i see the issues according to Sprint they were added first. Currently Issue 1 shows under Sprint 2.
||Sprint 1|Sprint2||
||Issue A|Issue B||
My final requirement is to calculate the avg story point for the last 3 closed sprints which am able to achieve. However, Issue A story points are being considered in Sprint 1 and Sprint 2. I want it to list only under Sprint 1 since this was the first sprint where issue was added.
Since you are looking for the first Sprint of the issue - you need to analyze each issue separately.
On the issue level, you need to compare if the current Sprint key equals the first item in the list of the issue property “Sprint IDs” and if it does - retrieve the Story points, but reset the Sprints dimension as that is not relevant anymore.
The expression needs some supplemental functions like CAST and SUM for compatibility reasons.
The expression on the level of an individual Issue might be as follows.
CASE WHEN
Cast(
[Sprint].[Sprint].GetMembersByKeys(
[Issue].CurrentHierarchyMember.Get('Sprint IDs')).Item(0).Key
as string)
=
Cast(
[Sprint].CurrentHierarchyMember.Key
as string)
THEN
Sum(
--reset the Sprints dimension
[Sprint].CurrentHierarchy.DefaultMember,
--the actual measure
[Measures].[Story Points created])
END
The calculation at the level of Sprint would require first filtering the relevant issues.
The expression to find the Story points where the specific sprint was the first for the issue might be as follows.
Sum(
--filter only the issues where this sprint was first
Filter(
--create the set of issues
DescendantsSet(
[Issue].CurrentHierarchyMember,
[Issue].[Issue]),
--condition for issue
--create a list of Sprints for the issue, take sprint key for the first of them, convert to string for compatibility
Cast(
[Sprint].[Sprint].GetMembersByKeys(
[Issue].CurrentHierarchyMember.Get('Sprint IDs')).Item(0).Key
as string)
=
--retrieve sprint key for the current sprint and convert to string for compatibility
Cast(
[Sprint].CurrentHierarchyMember.Key
as string)
),
--value for sum
--reset the Sprints dimension as that is already filtered
([Sprint].CurrentHierarchy.DefaultMember,
--the actual measure, applied to the current issue in iteration
[Measures].[Story Points created])
)
You might then apply this expression to each of the last three closed sprints within the AVG function.
I am trying to obtain something similar to the request above which is to calculate at the level of sprint, the historical story points where the specific sprint was the first for the issue with an added condition that the issue must also be committed during start of sprint. Can the expression be adjusted to fulfil this requirement?
I tried incorporating the default sprint issues committed measure as an added condition but it returns me the current story points of the issues rather than historical
--Sums the total SP of issues for the FIRST time going into a sprint out of the issues committed during the start of sprint
Cache(
Sum(
--filter only the issues where this sprint was first
Filter(
--create the set of issues
DescendantsSet(
[Issue].CurrentHierarchyMember,
[Issue].CurrentHierarchy.Levels("Issue")),
--condition for issue
--create a list of Sprints for the issue, take sprint key for the first of them, convert to string for compatibility
Cast(
[Sprint].[Sprint].GetMembersByKeys(
[Issue].CurrentHierarchyMember.Get('Sprint IDs')).Item(0).Key
as string)
=
--retrieve sprint key for the current sprint and convert to string for compatibility
Cast(
[Sprint].CurrentHierarchyMember.Key
as string)
AND
[Measures].[Sprint Story Points committed] > 0
),
--value for sum
--reset the Sprints dimension as that is already filtered
([Sprint].CurrentHierarchy.DefaultMember,
--the actual measure, applied to the current issue in iteration
[Measures].[Story Points created]
)
)
)
You were almost there.
The previous requirement was to see the current story points of the issue that historically had that sprint as the first one.
In your case, you need to find the historical context and you might use “Sprint Story points committed” measure instead of the following numerical part of the Sum.
--reset the Sprints dimension as that is already filtered
([Sprint].CurrentHierarchy.DefaultMember,
--the actual measure, applied to the current issue in iteration
[Measures].[Story Points created]
)
So the expression in your case might be as follows.
--Sums the total SP of issues for the FIRST time going into a sprint out of the issues committed during the start of sprint
Cache(
Sum(
--filter only the issues where this sprint was first
Filter(
--create the set of issues
DescendantsSet(
[Issue].CurrentHierarchyMember,
[Issue].CurrentHierarchy.Levels("Issue")),
--condition for issue
--create a list of Sprints for the issue, take sprint key for the first of them, convert to string for compatibility
Cast(
[Sprint].[Sprint].GetMembersByKeys(
[Issue].CurrentHierarchyMember.Get('Sprint IDs')).Item(0).Key
as string)
=
--retrieve sprint key for the current sprint and convert to string for compatibility
Cast(
[Sprint].CurrentHierarchyMember.Key
as string)
),
--value for sum - historicaly committed story points
[Measures].[Sprint Story Points committed]
)
)