Filter Issues that don't belong to the Time selected on Pages

Questions & Answers
1

Hi Team,
I have the following report that shows the Original Estimated Hours and the Remaining Estimated Hours. very simple… But I’m having trouble because the OEH and the REH were set on February for both tickets OR-2 and OR-3 and now both are not showing when I select ‘Mar 2022’ in the Page Filter

image
image1614×534 72.1 KB

Is there a way to show the OEH based on the Start date and Due date instead of the dates when those dates were created?

I tried with this formula but I didn’t have results:

NonZero(
   Sum(
 Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
  [Time].CurrentHierarchy.DefaultMember >= 
  [Measures].[Issue Start date] AND
  [Time].CurrentHierarchy.DefaultMember <= [Measures].[Issue due date]
),
-- counter by measure works as a filter as well:
([Measures].[Original estimated hours with sub-tasks],
     [Time].CurrentHierarchy.DefaultMember)
  )
)
2

HI @Daniel_Luevano

You are on the right track with the idea to filter issues by the start and due date properties and then sum up the “Original estimated hours” measure regardless of the selected time (with Time dimension DefaultMember in a tuple).

The only thing to change is the syntax of the filtering part. There are two aspects to take into account.

  1. Arithmetical comparison (< >=) can not be used directly to compare dates or check if a particular date belongs to a Time member. For that, specific functions must be used: logical functions to check if a particular date belongs/is before/after to the period of a selected Time member; DateCompare() to compare two dates, etc.

  2. To refer to the selected in the report Time period, use CurrentMember [Time].CurrentHierarchyMember ; DefaultMember refers to All Times.
    To use the Time member to compare dates using DateCompare() function, first you must retrieve the start date of that selected Time member; for that, use StartDate: [Time].CurrentHierarchyMember.StartDate

The calculation formula could be the following: DateBeforePeriodEnd is used to check if the issue Start date is before or during the selected time period (Time member is used in this function); DateCompare is used to compare if the issue due date is after the period has started (Time member start date is used in this function).

NonZero(
   Sum(
 Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- issue start date is before or during the selected time period
  DateBeforePeriodEnd([Measures].[Issue Start date], [Time].CurrentHierarchyMember)
  and
-- issue due date is after the start of the selected time period
  DateCompare([Time].CurrentHierarchyMember.StartDate, [Measures].[Issue due date])<=0),
-- counter by measure works as a filter as well:
    ([Measures].[Original estimated hours with sub-tasks],
     [Time].CurrentHierarchy.DefaultMember)
  )
)

Give it a try and ask if something does not work as expected!

Ilze , support@eazybi.com

1 Like
3

Fantastic!! Thank you so much, very useful that you guide me on how to build it, very much appreciate it.