Last closed sprints

Questions & Answers
1

I am using this defined member shown below for getting last X number of closed sprints, but am getting a warning. What is a better way to do this? I have also tried using between two dates but that gives me active sprints for some reason.

Do not use the other dimension Measures when defining a calculated member in Sprint . Create complex calculations in Measures .

Aggregate({
Tail(Order(
Filter([Sprint].[Sprint].Members,
[Measures].[Sprint Closed?] = ‘Yes’ AND
NOT isEmpty([Measures].[Sprint End Date])),
[Sprint].CurrentMember.get(‘End date’), BASC
), 5)
})

For reference here is the between 2 dates defined calc i am using that also isn’t working.

Aggregate(
Order(
Filter([Sprint].[Sprint].Members,
[Sprint].CurrentMember.getBoolean(‘Closed’) AND
NOT IsEmpty([Sprint].CurrentMember.get(‘Complete date’)) AND
DateBetween([Sprint].CurrentMember.get(‘Complete date’),
‘15-Apr-2019’,
‘23-Apr-2019’) ),
[Sprint].CurrentMember.get(‘Complete date’),
BASC
)
)

2

Hi Efrain,

This is true that a warning is displayed when using other dimension members in any other dimension than Measures. The reason for this is because of calculation sequence - calling complex calculations in any other dimension than Measures can have unexpected consequences when some measure is not yet calculated but is expected in the dimension filter.

Nevertheless, this is one of the cases when this warning can be ignored because all you are referencing to is Sprint properties, not other complex calculated Measure. To avoid warning altogether, you can call the Sprint Closed property and End or Completed date properties directly as you did in the second calculation.

Note that in the second calculation you are referencing to Complete date, not End date that you used in the first one. These are different dates and one of them might be empty still while the other is filled.
Also, it is best to use the DateParse function when specifying dates as strings. Try

Aggregate(
  Order(
    Filter([Sprint].[Sprint].Members,
      [Sprint].CurrentMember.getBoolean('Closed') AND
      NOT IsEmpty([Sprint].CurrentMember.get('End date')) AND
      DateBetween([Sprint].CurrentMember.get('End date'),
        DateParse('15 Apr 2019'),DateParse('23 Apr 2019'))),
  [Sprint].CurrentMember.get('End date'),
  BASC
))

Lauma / support@eazybi.com