First date of issues resolved as project start date

Questions & Answers
,
1

Hi,
we need the first date of issues resolved as project start date.
For this we created a calculated member which is very slow:

 Cache(
-- First date with issues resolved will be project Start date
 Filter([Time].CurrentHierarchy.Levels('Day').Members,
   [Measures].[Issues closed] > 0
 ).Item(0).StartDate 
)

From this topic: Changing Measures from Resolved powered to Closed powered - #3 by JohnMichael_Talarico i got the new version which i wanted to try but this doesn’t seem to work in my case

-- This won't work always
-- projects starts at the moment when the first issue is resolved from any In progress status
DateWithoutTime(TimestampToDate(
   ([Measures].[Transition from first timestamp],
    [Resolution].[(unresolved)],
     [Transition Field].[Resolution],
     [Transition Status.Category].[In Progress],
    [Time].CurrentHierarchy.DefaultMember)
  )
)

Any ideas on how to fix the second version or to fix the horrible performance of the first one?
Thank you! :slight_smile:

2

Hi @David_Brotzer,

Each of those methods slightly differs; I will try to explain, an then you chose which would be more accurate for your use case.

  1. The issue resolution date is widely used in eazyBI to count resolved and open issues, completed story points, or other quantitative values by issue resolution date.
    For the first method, I would recommend using measure “Issues resolved” instead of “Issues closed”. And slightly adjust the expression to eliminate any other variables in the report (page filters and dimension on report rows and columns) except for selected projects; this could be done with the function DefaultContext().
    This expression will give the earliest resolution date among selected project issues, regardless of issue type, current status, assignee, or other dimension in the report:

    Cache(
-- First date with issues resolved will be project Start date
  Filter([Time].CurrentHierarchy.Levels('Day').Members,
    DefaultContext((
      [Measures].[Issues resolved],
      [Project].CurrentHierarchyMember,
      [Time].CurrentHierarchyMember
    )) > 0
  ).Item(0).StartDate 
)

  2. In the second option, eazyBI looks at issue change history and captures date when a project issue moved out of a In Progress status category and got some value in field resolution. This solution might work in specific project setup but not elegant for universal application.

    • Sometimes Resolution field is not updated when issue is completed (depends on workflow settings). It might be that some issues have resolution like Won’t Do, while they are in status category To Do, or resolution is set later after the issue is moved to Done category.
    • Measure “Transition from first timestamp” is context sensitive and would be affected with values you have selected on report pages, rows, and columns. it is smiliart problem as with first solution with measure “Issue closed” (“Issue resolved”).

    If yuo are lookg for the first date when some issue got value in Reolution field, consider updated expression:

    -- projects starts at the moment when the first issue gets value in resolution field
DateWithoutTime(TimestampToDate(
 DefaultContext((
   [Measures].[Transition from first timestamp],
   [Resolution].[(unresolved)],
   [Transition Field].[Resolution],
   [Project].CurrentHierarchyMember
 ))
))

    Or alternative to get date when an issue moved to Done status category for the first time:

    -- projects starts at the moment when the first issue moved to any of Done statuses
DateWithoutTime(
 DefaultContext((
   [Measures].[Transition to status first date],
   [Transition Status.Category].[Done],
   [Project].CurrentHierarchyMember
 ))
)

Here are more details on used function DefaultContext() that alowes ignore the rest of report context and focus solely on Project and the action that marks the start of project: DefaultContext.

Best,
Zane / support@eazyBI.com