How to create a measure counting all open issues which are in Submitted more than 10 working days?

Questions & Answers
1

I need to measure open issues which were in Submitted longer than 10 working days.
How do I code the MDX?

2

Hi @Annette

Welcome to eazyBI Community!

Can you please clarify your requirement? Are you looking for:

  1. Issues that are currently open and still have a status of “Submitted” for more than 10 working days?

OR

  1. Issues that are currently open but are no longer in “Submitted” status, where you want to count those that spent more than 10 working days in “Submitted” status at some point in their history?

Best wishes,

Elita from support@eazybi.com

3

Yes, the first one.
Issues that are still in submitted for more than 10 days

4

Hi,
I created now some measures.
– days in current status from last transition for open issues

>10:

IIf(
( [Status].[Submitted] > 0

and
– calculated days in status
DateDiffDays([Measures].[Issue created date], Now()) >10),1,0

and

Late Submitted:
sum([Measures].[>10])

It gives me

image
image329×651 12.1 KB

BUT, when I want to sum it up over all Defects, it gives ,e Zero.
I guess it is because easyBI calculates the average and the average is not >10.

How can I get the sum of issues in submitted longer than 10 days without showing all issues?

5

Hi @Annette

I apologize for the delayed response; I missed a notification of your reply.

Please try defining a new calculated measure in the Measures dimension and check if it returns the expected results for you.

Count(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- Issue is currently in Submitted status
    [Measures].[Issue status] = 'Submitted'
    AND
    -- Issue is not resolved (open)
    IsEmpty([Measures].[Issue resolution date])
    AND
    -- Time in current status is more than 10 working days
    DateDiffWorkdays(
      [Measures].[Issue status updated date],
      Now()
    ) > 10
  )
)

Best wishes,

Elita from support@eazybi.com

6

Thanks for your answer,
This is now counting over all issue types, though I limited to Defects

image
image589×130 6.11 KB

7

Changing to

Descendants([Issue].CurrentMember, [Issue Type].[Defect]),
– Issue is currently in Submitted status
[Measures].[Issue status] = ‘Submitted’

gived result 0, though there are 4 late defects

8

Hi @Annette
Thanks for the follow-up and for the screenshot!

Please try adjusting the measure to below and see if it returns the expected results.

Count(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- Issue is currently in Submitted status
    [Measures].[Issue status] = 'Submitted'
    AND
    -- Issue is not resolved (open)
    IsEmpty([Measures].[Issue resolution date])
    AND
    -- Time in current status is more than 10 working days
    DateDiffWorkdays(
      [Measures].[Issue status updated date],
      Now()
    ) > 10
  ) AND
  [Measures].[Issues created] > 0 
)

Best wishes,

Elita from support@eazybi.com

9

That worked, when I sat the second last ) correctly.
Thanks for all your help!