Create formula for unplanned parallel work

Questions & Answers
,
1

Hi,

I am trying to build a formula that take active sprint value and component value from filter and shows count of tickets which have the same component and were updated during the dates of the selected sprint but has the sprint value as empty.

So basically it gives me tickets which were being worked upon during the sprint but were not included in the sprint. I have created below formula but it is not giving me results. I receive “mmm dd yyyy” as output. Please help in achiving the desired results.

JQL example:
project = “ABC” AND updated >= ‘2025-04-16’ AND updated <= ‘2025-04-29’ AND component = Tech AND Sprint is EMPTY
In above JQL I am adding the sprint dates manually but MDX should obtain these date values from the selected sprint in filter.

MDX formula:

Count(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
– Filter issues updated during the selected sprint’s date range
DateCompare(
[Measures].[Issue updated date],
[Sprint].CurrentHierarchyMember.Get(‘Start date’)
) >= 0
AND
(
IsEmpty([Sprint].CurrentHierarchyMember.Get(‘End date’))
OR
DateCompare(
[Measures].[Issue updated date],
[Sprint].CurrentHierarchyMember.Get(‘End date’)
) <= 0
)
AND
– Filter by component selected in filter
[Measures].[Issue components] MATCHES
[Component].CurrentHierarchyMember.Name
AND
– Filter issues with no sprint value
IsEmpty([Measures].[Issue Sprint])
)
)

Thanks in Advance!

2

Hi @aditya.verma

I’ll share here the same formula that I shared in the email:

Sum(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    -- Filter issues updated during the selected sprint's date range
    DateBetween(
      [Measures].[Issue updated date],
      [Sprint].CurrentHierarchyMember.Get('Start date'),
      -- use Sprint actual end date, if there is none, then planned end date, if there is none then Now()
      CoalesceEmpty(
        [Sprint].CurrentHierarchyMember.Get('Complete date'),
        [Sprint].CurrentHierarchyMember.Get('End date'),
        Now()
      )
    )
  ),
  CASE WHEN
    -- if the issue has no sprints
    [Measures].[Issue Sprint] = "(no sprint)"
  THEN
    -- return the count of the issue (this will match with the component as well)
    (
      [Measures].[Issues last updated],
      [Sprint].CurrentHierarchy.DefaultMember
    )
  END
)

Be sure to set the Formatting to Numeric → Integer to see numeric values instead of “mmm dd yyyy”

​Best regards,
​Nauris