I am trying to create an Epic Velocity Report based on Sprints.
All the Epics have baseline end dates that fall within a specific Sprint. I want to show how many were resolved on time (within the sprint dates) and also how many were closed late (past the sprint closed date). I am using resolved dates and get close but not quite there.
I am using time dimension, so each of those are sprint dates. Baseline End Epics are the count of Epics with a baseline end within that time frame. This number is correct.
On time is just showing the number of Epics that are closed but not restricted to those dates. 3 should be the number for on time in 25.1.1.
This is the formula:
CASE
WHEN
[Measures].[Issues with Baseline End] <> 0
THEN
SUM(Filter(
[Fix Version].[Version].Members,
DateInPeriod(
[Fix Version].CurrentMember.get(‘Release date’),
[Time].CurrentHierarchyMember
) AND ([Time].CurrentHierarchy.DefaultMember,
[Measures].[Issues created]) > 0),
– open issues in version at release date
CoalesceEmpty(([Measures].[Open issues],
[Time].CurrentHierarchy.Levels(“Day”).DateMember(
[Fix Version].CurrentMember.get(‘Release date’)
)),0 )
)
END
I used a CASE WHEN statement just because when I use the SUM Filter it takes too long to load that it times out and that means I can not drill down to see those issues. However, the CASE WHEN still didn’t fix that.
Any help is appreciated!
Thanks
Hi @Kayris06
Welcome to eazyBI community.
It is not clear from your screenshot what dimension you use in report rows.
And why would you use the release date from the fix version if you want to check the epic baseline date within the sprint time frame? Why don’t you calculate the timeframe for epics from sprint start and end dates? And where do you filer the report for the “specific sprint”?
Without these additional details and your report definition, it is hard to recommend any code for you
Please contact support@eazybi.com and provide more details about this requirement.
Martins / eazyBI
I might have pasted the wrong measure formula to this. I was using that on another Epic Velocity report that does use release date.
The dimensions used are, Pages - Projects and Team / Row - Time
Each Epic has a Baseline End Date. So the measure “Baseline End Epics” there is pulling in all Epics with a Baseline End Date (that falls within the custom time dimensions). Each of those rows is a sprint that include all dates between start date and end date of that sprint. The Baseline End Epics measure is correct. Now I want to show On Time as any Epic that has a Baseline End Date within that sprint time dimension that also closed within that sprint time dimension.
Example: out of the 14 in 25.1.1, how many of those were resolved in that same 25.1.1 sprint and how many closed after 25.1.1.
Additionally, I want to see everything with a Baseline End Date in that sprint time dimension that did not close within that sprint time dimension, still open and those that are closed(late).
I was successful doing this on a Fix Version level pretty easily but I am having trouble with the sprints.
Let me know if there is anything else that’s needed. Its hard for me too show too much because my job has sensitive data I can not share.
Thanks
Hi @Kayris06
Please share the report definition.
I believe you coud use the Descendants function for “On Time” calculation to get the list of issues that both were resolved and have a Baseline end date in the period.
Try this formula for your calculated measure “On Time”
CASE WHEN
[Measures].[Issues with Baseline End]>0
THEN
Sum(
Filter(
Filter(
DescendantsSet(
IIf(
[Project].CurrentMember.Level.Name = "Project",
IIf(
[Project].CurrentMember.Name = "AggregatePageMembers",
[Issue].[Project].GetMembersByKeys(
Generate(
ChildrenSet([Project].CurrentMember),
[Project].CurrentMember.GetString('DISPLAY_KEY'),
","
)
),
[Issue].[Project].GetMemberByKey(
[Project].CurrentMember.Key
)
),
[Issue].CurrentMember
),
[Issue].[Issue]
),
DateInPeriod(
[Measures].[Issue Baseline End],
[Time].CurrentHierarchyMember
)
),
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember
)
),
[Measures].[Issues with Baseline End]
)
END
It filters issues by both properties Baseline End and Resolution date within each period.
Martins / eazyBI
This works perfectly! Thank you so much. Will this work on a release version level too?
The reason i love it is because it doesn’t time out anymore!
@Kayris06
Yes, the same code should work also with Fix version dimension.
Martins / eazyBI
