Count of issues due on or before the upcoming Sunday

Questions & Answers
1

Hi,

Trying to get a calculated measure to pull in all tests that are in specific status values that are due on or before the upcoming Sunday.

Using the “issues due” does not work for what i need since that removes items that are “done” from a jira perspective.

Need to so something like count(issues with due date <= this week and status in (pass,fail,no run, blocked).

using the time filters gets close but then again i run into the issue with the “done” no matter which i use for the overall and i have other measures that are not part of the time dimension (total remaining, total in n/a which are regardless of the actual due date).

Most likely i think i need to use something like getting the count of due with status of pass + count of due with status of no run etc. etc.

Thanks,
Dennis

2

I believe I am part way there based on some other items I located on community.

I have created a new measure in the status dimension to look at the issues in the specific status values we need.

Aggregate(
{
[Status].[No Run],
[Status].[Pass],
[Status].[Fail],
[Status].[In Progress]
}
)

Not sure how to filter what i need next…

Basically i need to count all the issues with a due date on or before this week and filter by those in the aggregation above.

Thanks,
Dennis

3

Here is what i have in addition to the new measure above in the status dimension

Created a new measure using the following:

Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.get(‘Due date’),
[Time.Weekly].[Week].CurrentHierarchyMember
)AND
([Measures].[Issues created],
[Status].[Tests Total])>0
)

), [Measures].[Tests Total]

Confirming the data now but appears to be correct - can anyone confirm that the combination i have will give me anything that has a due date up to and including this coming Sunday that is in any of the status values (No Run, Pass, Fail, In Progress)?

Thank you.

4

Unfortunately the formula above is working but it is also counting items that have a future due date as well.

Need to add another filter or modify the existing filter statement to only count those in the specific status values of the Tests Total custom value who have a due date up to this week.

5

Hi,

Try using DateBeforePeriodEnd function in the filter conditions.

Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateBeforePeriodEnd(
[Issue].CurrentHierarchyMember.get('Due date'),
[Time.Weekly].[Week].CurrentDateMember
)AND
([Measures].[Issues created],
[Status].[Tests Total])>0
),
 [Measures].[Tests Total]
)

Martins / eazyBI

6

Thanks Martins,

This one is what i am using to ensure i am only pulling items that have due dates as well.

Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateBeforePeriodEnd(
[Issue].CurrentHierarchyMember.get(‘Due date’),
[Time.Weekly].[Week].CurrentDateMember
)AND ([Measures].[Issues with due date],[Status].[Tests Total])>0
) ,
[Measures].[Tests Total]
)

Working properly now.

Dennis