Count the number of issues expected on time for the period

Questions & Answers
1

Hi everybody,

I meet a problem with a measure on Eazy BI. I would like to count the number of issue expected on time for the period so this is my formula :
"Count(
Filter(
[Issue].CurrentHierarchyMember,

DateDiffDays(
[Issue].CurrentHierarchyMember.Get(‘Due date’),
[Issue].CurrentHierarchyMember.Get(‘Initial due date’))=0
AND [Measures].[Issues with initial due date] >0
)
)"
The problem is that the results are always 0 rather if the number of task on time are more than 0.

Can somebody help?

Thank you,

Muriella

2

@Muriella

Try this code for your calculated measure.

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentHierarchyMember.Get('Initial due date'),
      [Time].CurrentHierarchyMember
    )
   AND
    DateDiffDays(
      [Issue].CurrentHierarchyMember.Get('Due date'),
      [Issue].CurrentHierarchyMember.Get('Initial due date')
    )=0
  ),
  CASE WHEN
  [Measures].[Issues with initial due date] >0
  THEN
  1
  END
)

It has “Descendants” function to go through all Issue dimension members at issue-level and filter the right set for your calculation based on the condition DatediffDays
When selecting this measure, make sure that “Nonempty” cross join is enabled for report rows to optimize the query.

It would filter issues that have initial due date in the displayed time period and check if due date is the same as initial due date

Martins / eazyBI

1 Like
3

Thank you for your answer, it helped me a lot!

Now I meet another challenge. I have this graph :

image
image1498×385 27.5 KB

Here, I have all the document that have been delivered on time, late and those who are late and not delivered before the line “Aujourd’hui” and those that are planned to be on time, late for the future.

For the measure, I used yours and I added some new function, this is the measure for the document that are planned to be on time in the future :

"Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.Get(‘Due date’),
[Time].CurrentHierarchyMember
)
AND
DateDiffDays(
[Issue].CurrentHierarchyMember.Get(‘Due date’),
[Issue].CurrentHierarchyMember.Get(‘Initial due date’)
)>=0
),
CASE WHEN
[Measures].[Issues with due date] >0 and (DateBeforePeriodEnd(“Today”, [Time].CurrentHierarchyMember)
OR
DateInPeriod(“Today”, [Time].CurrentHierarchyMember))

THEN
1
END
)"

My problem is that I would like to consider the current month as a future month because the result of the request in this month combine the measure for the future and the past and I don’t want that… Do you know how to add this condition?

Thank you,

Muriella R.

4

@Muriella

If by “Current month” you mean the month displayed in report, you can use the formula [Time].CurrenthierarchyMember.NextMember to dynamically call the next member than the one displayed on x-axis.

Martins / eazyBI

5

Can we include this SUM function in CASE statement like below:

CASE WHEN
NOT IsEmpty([Measures].[Issue Revised Action Due Date]
THEN
SUM(Filter(
Descendants([Issue].DefaultMember, [Issue].[Issue]),
NOT IsEmpty([Measures].[Issue Revised Action Due Date]) AND
(DateDiffDays(“today”,[Measures].[Issue Revised Action Due Date])>0 AND
DateDiffDays(“today”,[Measures].[Issue Revised Action Due Date])<=30)

),
CASE WHEN [Measures].[Issues with revised action due date]>0
THEN 1
END
)
when IsEmpty([Measures].[Issue Revised Action Due Date]
THEN
Sum(Filter(
Descendants([Issue].DefaultMember, [Issue].[Issue]),
IsEmpty([Measures].[Issue Revised Action Due Date]) AND
(DateDiffDays(“today”,[Measures].[Issue Action Due Date])>0) AND
DateDiffDays(“today”,[Measures].[Issue Action Due Date]<=30)
),
CASE WHEN [Measures].[Issues with action due date] > 0
THEN 1
END
)