Average of calculated Members

Hi,
i am trying to get the average of Sprint Story points committed

Average velocity is a calculated measures i have created and the mdx is as follow:

NonZero(
Avg(
Filter(
Descendants([Sprint].CurrentHierarchyMember),
[Measures].[Sprint Story Points committed]>0
)
,
[Measures].[Sprint Story Points committed]
))

Under rows ‘Last 6 sprint’ is a calculated members i have created. The MDX is as followL

Aggregate(
Order(
Filter([Sprint].[Sprint].Members,
NOT IsEmpty([Sprint].CurrentMember.get(‘Start date’)) AND
DateBetween(
[Sprint].CurrentMember.get(‘End date’),
‘13 weeks ago’,
‘Now’)
),
[Sprint].CurrentMember.GetDate(‘Start date’),
ASC
)
)

Hi @whosiao

You are on the right track, the only thing is to correctly retrieve all sprints of the “Last 6 Sprints”.
To do that, use function ChildrenSet().
The following construction would be more general (for calculated members, as well as Sprint standard members):

NonZero(
 Avg(
 Filter(
  Generate(
    ChildrenSet([Sprint].CurrentHierarchyMember),
    Descendants([Sprint].CurrentHierarchyMember, [Sprint].[Sprint])),
  [Measures].[Sprint Story Points committed]>0),
  [Measures].[Sprint Story Points committed]
 ))

Also, you may check out the “Last 5 closed” calculated member from Sprint dimension in the demo account for another approach how to retrieve last 6 sprints.

Ilze / support@eazybi.com

Thank you for the suggestion. ChildrenSet() does work for me.