Please help me create a calculated measure for the number of overdue problems

[jon33367]

Good afternoon
Please help me create a new measure. To count the number of overdue tasks, in my head I roughly understand what it should look like, but in practice I’m stuck.

For tasks that are in the status before Done, I need the following formula: [Measures].[Start date] - ([Measures].[Pause end date - pause start date]),
and if the status of the task is Done, then according to the following formula:
[Measures].[Start date] - ([Measures].[Pause end date - pause start date])- [Measures].[Decision date]
and if both formulas are more than 60 days from the start date, then it is expired.

This needs to be somehow fit into 1 measure. The problem I have is that all the fields that are listed have a date format, and I need to get the total value for the month of how many tasks were overdue.

Please help me write this measure to complete the task.

[zane.baranovska]

Hi @jon33367,

When comparing different dates assigned to an issue, this kind of logic should be applied to individual issues. For the calcaution:

• use the function Descendants() to iterate through individual issues
• use aggregate function Sum() or Count() to get a count of issues that match described logic.
• use the function DateDiffDays() to calculate the duration between two dates
• use CASE WHEN / THEN statement to sort issues in done or still ongoing issues.

I understand that you have two date fields: Start date and Decision date, and “Pause end date - pause start date” is already the duration of how long the issue was paused. In that case, the structure for calculation might look like this:

Count(
  Filter(
    --iterate through a set of all issues
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    --check issue has a Start date
    NOT IsEmpty([Issue].CurrentMember.Get('Start date')) AND
    --for each issue, calculate if the duration is more than 60 days
    (
      --duration from Start date till Decision date or today
      DateDiffDays(
        [Issue].CurrentMember.GetDate('Start date'),
        CASE WHEN --check the issue current status
          [Measures].[Issue status] = 'Done'
        THEN --decision date for Done issue
          [Issue].CurrentMember.GetDate('Decision date')
        ELSE --today, for the rest of the issues
          DateParse('today')
        END
      ) 
      --subtract duration while paused
      - [Measures].[Pause end date - pause start date]
    ) > 60
  )
)

Set measure formatting to an integer. Please use autocomplete functionality to update the expression with property and measure names you have in your eazyBI. More details on calcauted measures are described in the documentation: Calculated measures.

Best,
Zane / support@eazyBI.com

[jon33367]

Good afternoon !

Thank you, but for some reason I get an error when creating a measure.

Maybe this explanation will suit you better.

It is necessary to fit everything into 1 measure, and it should display the number of tasks that occurred more than 60 days from the start date

Algorithm for calculating repetitions

1. If the task is not completed, consider the date today - minus the number of days for pauses and minus the start date.
2. If the task is closed, the status is ready - STP approval date - minus the number of days for pauses and minus the start date.

[zane.baranovska]

Make a slight adjustment to line 6; use the function Get() instead of GetDate() to check if an issue has such a property.
modified line:

...
--check issue has a Start date
NOT IsEmpty([Issue].CurrentMember.Get('Start date'))
...

I updated the code in my response, too.

[jon33367]

Good afternoon !
It worked, but it does not display the total quantity, it writes the following error (in the screenshot)