Sum of Hours for Issues with earlier due date

Questions & Answers
, ,
1

Hi,

I’m importing some Jira ticket with a couple of custom fields (import as Measure and property).
I now want to show a list of all tickets (Issues in rows) with the following columns:

“Name_Val” (customfield string): [Issue].CurrentHierarchyMember.get(‘Name_Val’)
“Stage_Val” (customfield integer): [Issue].CurrentHierarchyMember.get(‘Stage_Val’)
“Due date” (normal jira field): [Issue].CurrentHierarchyMember.get(‘Due date’)
“Story Points” (normal jira field): [Issue].CurrentHierarchyMember.get(“Story Points (Portfolio)”)

All of them showing correctly in my table.

The 5th column should, in each row, show the sum of StoryPoints up to the date of the current rows due date but only for the same Name_Val and Stage_Val.
In other words get the current rows Name_Val and Stage_Val and iterate over all tickets with the same Name_Val and Stage_Val and sum the StoryPoints, if the due date of the iterated ticket is earlier or the same as the current rows due date.

I tried a lot, most of it with the help of ChatGPT, but can’t get it to work as I’m lacking deeper knowledge and mdx is very unforgiving for even slightest syntax mistakes without debugging options.
The closest I got so far (which might be completely wrong), is this code below summing up all tickets no matter the Name_Val and Stage_Val combination. If I set a fixed Name_Val and Stage_Val, the Story Points are summed up correctly.

Sum(
  Filter(
    [Issue].[Issue].Members,
    CoalesceEmpty([Issue].CurrentMember.get('Name_Val'), '') = CoalesceEmpty([Issue].CurrentHierarchyMember.get('Name_Val'), '')
    AND
    CAST([Issue].CurrentMember.get('Stage_Val') AS INTEGER) = CAST([Issue].CurrentHierarchyMember.get('Stage_Val') AS INTEGER)
    AND
    DateCompare(
      [Issue].CurrentMember.get('Due date'),
      [Issue].CurrentHierarchyMember.get('Due date')
    ) <= 0
    AND NOT IsEmpty([Issue].CurrentMember.get('Story Points (Portfolio)'))
  ),
  [Issue].CurrentMember.get('Story Points (Portfolio)')
)

Thanks for any help in advance!

2

With some other Q&A, I was able to get it working. You have to create the reference of the current cell from a higher hierarchy:

NonZero(
  Sum(
  [Issue.Epic].[Parent].GetMemberbyKey([Issue].CurrentHierarchyMember.Key),
  Sum(
    Filter(
      [Issue].[Issue].Members,
      DateCompare(
      [Issue].CurrentMember.get('Due date'),
      [Issue.Epic].[Parent].CurrentMember.Get('Due date')
      ) <= 0
      AND
      [Issue].CurrentMember.Get('Name_Val') MATCHES [Issue.Epic].[Parent].CurrentMember.Get('Name_Val')
      AND
      [Issue].CurrentMember.GetString('Stage_Val') MATCHES [Issue.Epic].[Parent].CurrentMember.GetString('Stage_Val')
      
        ),
      [Issue].CurrentMember.Get('Story Points (Portfolio)')
      )
    )
  )
1 Like