EazyBI: Measure to count issues that are created and due/expiring in the same month.

[Marc Lim]

Hi, I'm new to mdx language and eazybi in general. I would like to filter my issues across a time period of 12 months. The issues displayed per month should only be those that are created in that month and due for expiry in the same month. E. G for the month of Feb I would want to see all issues created this month, which have a due date in the month of Feb.

Would appreciate some help on this as I'm not sure where to start. Thanks in advance for advices/pointers!

[Roberts Čāčus · Accepted solution]

Hi @Marc Lim ,

To create the report you are looking for, try to put the Time dimension on rows with "Month" level selected. To get the count of issues, try to create a new calculated member in the Measures dimensions, please have a look a the formula below:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateInPeriod(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember
    )
    AND
    DateInPeriod(
      [Measures].[Issue due date],
      [Time].CurrentHierarchyMember
    )
  ), [Measures].[Issues created]
)

The report could look similar to the one below:

Kind regards

Roberts // eazyBI support

[Marc Lim]

Thank you kindly for the reply. The answer worked.

[Sekhar Chandra]

Hi Marc

You can use JQL query for what type of issues you want to get into a list. Write a JQL in Issue Navigator window, search bar .

I provide a sample example query for your reference. I hope this would help you.

Please replace the dates as you require.

"created >= 2019-07-01 AND created <= 2019-07-31 AND due >= 2019-7-1 AND due <= 2019-7-31 order by created DESC"

Chandra