Automatically select current sprint

Questions & Answers
2

Hi,

You can create the calculated member in the Sprint dimension giving dynamically sprints by some condition. For instance, following formula would give you the ordered list of all active sprints currently (there might be several of them):

Aggregate(
  Order(
    Filter(
      [Sprint].[Sprint].Members,
      NOT [Sprint].CurrentMember.getBoolean('Closed')
      AND
      NOT isEmpty([Sprint].CurrentMember.get('Start date'))
    ), 
    -- ordering by start date across all boards
    [Sprint].CurrentMember.get('Start date'), BASC
  )
)

This calculation gives the ascending list ordered by the Sprint start date.
Similarly, you can extend the formula by a condition of when the sprint is completed to find the sprints active in some of the previous periods and apply the “Item(0)” function to get the first sprint from the list:

Aggregate(
  Order(
    Filter(
      [Sprint].[Sprint].Members,
      [Sprint].CurrentMember.getBoolean('Closed')
      AND
      DateBetween([Sprint].CurrentMember.get('Complete date'),'14 days ago','now')        ), 
    -- ordering by complete date across all boards
    [Sprint].CurrentMember.get('Complete date'), BDESC
  ).Item(0)
)

Note the “DateBetween” function in the condition where you can set the date range dynamically for the period you are interested.

You can also check our demo account for some more ideas of how to create members in the Sprint dimension: https://eazybi.com/accounts/1000/cubes/Issues.

image
image.png1022×564 31 KB

Kindly,
Janis, eazyBI support