Help Display Tasks Past Due based on a Custom Field

Questions & Answers
1

Hello, I’m trying to create a new measure that will display the tasks that are in a status of In Progress and past due based on a date entered in a field called Target Prod/Completion Date compared to todays date along with displaying the number of days the task is past due. When I attempt the code below, it includes the tasks that don’t have a Target Prod/Completion Date and the tasks that have a future Target Prod/Completion Date. What am I missing to exclude those two items?

NonZero(Aggregate(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Issues created]>0
),
CASE
WHEN [Measures].[Issue status] = “In Progress”
THEN
DateDiffDays([Measures].[Issue Target Prod/Completion Date], Now())
END
))

2

Hi @Alyssa_A,

You are on the right track with the calculation. There are a few improvements you might want to make.

  1. To exclude issues without a Target Prod/Completion Date, you might want to add one more condition to the Filter that Target Prod/Completion Date has value less than today. For the date comparison use function DateBeforePeriodEnd().

  2. Validations by issue status, move to the Filter as well. In calculated measures, I recommend filter by issue properties first to make the calculation work faster.

  3. Use aggregate function Avg() to get average value over several issues or Sum() to see the total amount of days over several issues.

  4. If you have imported Target Prod/Completion Date also as a measure, use predefined measure “Issues with Target Prod/Completion Date” for a calculation instead of “Issues created”. This also will reduce the set of issues to issues with some value in the date field.
    In any case, the calculation should refer to some predefined measures (that is not property) so calculated measures would respond to the selected values on report rows, columns, and pages ().

The updated formula might look like this:

NonZero(Avg(
  --filter set of issues by the properties status and target date
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    [Measures].[Issue status] = "In Progress" AND
    DateBeforePeriodEnd(
      [Measures].[Issue Target Prod/Completion Date],
      [Time].[Day].CurrentDateMember) 
  ),
  --is issue has target date then caclaulte how much it is overdue
  CASE WHEN [Measures].[Issues with Issue Target Prod/Completion Date] > 0
  THEN 
    DateDiffDays([Measures].[Issue Target Prod/Completion Date], Now())
  END
))

Documentation on calculated measures are here: https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members

Best,
Zane / support@eazyBI.com

3

Thank you so much! This was very helpful