How can I create a report showing only issues that have Due Dates after the previous month?

Questions & Answers
1

Hello,

Is there a way to create a report showing only issues having Due Date after the previous month to future?

For example, today is September 6, 2024, so I want to show only issues that have Due Date after August 31, 2024.

I don’t want to insert a specific date as a standard, but the system should automatically detect today’s month and consider that.

Thank you!

1 Like
2

Hi!
What about having a custom measure like this:

Count(Filter(
  Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
  DateAfterPeriodEnd(
    [Issue].CurrentMember.Get('Due date'),
    [Time].[Month].CurrentDateMember.PrevMember
  ) AND 
   not IsEmpty([Issue].CurrentMember.Get('Due date'))
))

Basically you are using the Date After Period End to check it true or false

1 Like
3

Hi @Kyung_Park,

The solution offered by @Nacho is a good start and might work great when you do not have other dimensions or filters in the report.

If some other dimensions apply, you might need to develop that approach further.
You might convert Count() into Sum() and apply the measure [Measures].[Issues with due date] to retrieve the relevant issues only.

A slightly more efficient option would be to pre-filter issues by the existence of Due date and then check if the Due date is relevant and only then call for the measure.

The expression might then be as follows.

Sum(
 Filter(
  DescendantsSet(
   [Issue].CurrentHierarchyMember,
   [Issue].[Issue]),
--primary condition - due date populated
 NOT IsEmpty([Issue].CurrentMember.Get('Due date'))),
--numeric value for sum - executed only for issues with due date
 CASE WHEN
  DateAfterPeriodEnd(
    [Issue].CurrentMember.Get('Due date'),
    [Time].[Month].CurrentDateMember.PrevMember)
  THEN
--the measure that applies report context
   [Measures].[Issues with due date]
  END
 )

However, if you are on Cloud or using the latest version of eazyBI (7.2 or later) a new function FuturePeriods() was introduced.
You might read more about that here - FuturePeriods.

Then, you might drop the need to iterate through the issues.

In that case, the expression might be as follows.

Sum(
--current month
{[Time].[Month].CurrentDateMember,
--set of future time periods
  Futureperiods([Time].[Month].CurrentDateMember)},
--measure for sum
 [Measures].[issues with due date])

This would work even better.

Regards,
Oskars / support@eazyBI.com

1 Like