Extracting the Earliest Fix Version Start and Latest Release Date

I am continuing to develop an S-Curve based on weight status of issues. For the S-Curve calculation I need to supply a start and end date not based transition starts or resolutions dates as in the eazyBI example S-Curve, but on dates given by the development effort itself. I think the best way is for my context (non-Agile) is to use dates from Fix Versions. In the report so far, I used hard coded start and dates just to get the prototype working. I want to replace the [Measures].[Planned end date] and [Measures].[Actual start date] with the earliest Fix Version start date and the latest Fix Version release date. I’ve tried to adapt examples based on Sprint dates but can’t seem to get those to work.

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Project Prediction report",
     "folder_name": "Project Management",
     "result_view": "line_chart",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[S-curve guideline]","[Measures].[Today]","[Measures].[Weighted Status]","[Measures].[Project Start]","[Measures].[Project End]"],"members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time.Weekly].[Week].Members"],"members":[],"bookmarked_members":[]}],"filter_by":{"conditions":[{"expression":["[Measures].[Time in project]"],"operator":"\u003e","value":"0"}],"others":false},"nonempty_crossjoin":false},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"drillable":true,"drilled_into":false,"expanded":true,"full_name":"[Project].[All Projects]","name":"All Projects","type":"all"}],"bookmarked_members":[],"current_page_members":["[Project].[All Projects]"]},{"name":"Issue Type","selected_set":["[Issue Type].[All Issue Types]"],"members":[{"depth":0,"drillable":true,"full_name":"[Issue Type].[All Issue Types]","name":"All Issue Types","type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Issue Type].[All Issue Types]"]},{"name":"Fix Version","selected_set":["[Fix Version].[All Fix Versions]"],"members":[{"depth":0,"name":"All Fix Versions","full_name":"[Fix Version].[All Fix Versions]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Fix Version].[All Fix Versions]"]}]},"options":{},"view":{"current":"line_chart","maximized":false,"line_chart":{"area":false,"swap_axes":false,"data_labels":false,"series_options":{"Project End":{"type":"plotline","dataLabelType":"top vertical"},"Weighted Status":{"symbol":false,"type":"area"},"S-curve guideline":{"symbol":false,"type":"line-Dash"},"Today":{"type":"plotline","dataLabelType":"top vertical"},"Project Start":{"type":"plotline","dataLabelType":"top vertical"}}},"table":{}},"calculated_members":[],"description":"Project prediction is calculated using the average resolution pace in the project between the project start date and the current date and multiplied by days in the displayed period before predicted project end date."}
  } ],
  "calculated_members": [{"dimension":"Measures","name":"Issues history","format_string":"#,##0","formula":"Cache(\n  NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n    [Measures].[Transitions to]\n    - [Measures].[Transitions from]\n  ))\n  + [Measures].[Transitions to]\n  - [Measures].[Transitions from]\n)"},{"name":"Today","dimension":"Measures","formula":"--annotations.group= S-Curve\nCase WHEN\nDateInPeriod(\n'today',\n[Time].CurrentHierarchyMember\n)\nTHEN\n\"Today\"\nEND","format_string":"#,##0"},{"name":"Time in project","dimension":"Measures","formula":"--annotations.group= S-Curve\nCase when\n  DateBetween(\n    [Time].CurrentHierarchyMember.StartDate,\n    [Measures].[Actual start date],\n    [Measures].[Planned end date])\n  or\n  DateInPeriod(\n    [Measures].[Actual start date],\n    [Time].CurrentHierarchyMember\n    )\n Then\n 1\n End","format_string":"#,##0"},{"name":"S-curve guideline","dimension":"Measures","formula":"--annotations.group= S-Curve\n([Measures].[Issues created],\n[Time].CurrentHierarchy.DefaultMember)\n/\n(1+Exp(-DateDiffDays(\n  DateAddDays(\n    [Measures].[Actual start date],\n      -- \"2\" assume that s-curve meets the linear prediction in the middle\n    DateDiffDays([Measures].[Actual start date],\n      [Measures].[Planned end date])/2 \n  ),\n -- \"50\" is parameter of how steep the curve will be\n  [Time].CurrentHierarchyMember.StartDate)/36\n))","format_string":"#,##0.00"},{"name":"Weighted Status","dimension":"Measures","formula":"([Measures].[Issues history],\n[Transition Status].[In Review])*0.05\n+\n([Measures].[Issues history],\n[Transition Status].[Refining])*0.1\n+\n([Measures].[Issues history],\n[Transition Status].[Ready])*0.15\n+\n([Measures].[Issues history],\n[Transition Status].[On Hold])*0.2\n+\n([Measures].[Issues history],\n[Transition Status].[In Progress])*0.3\n+\n([Measures].[Issues history],\n[Transition Status].[Test Ready])*0.5\n+\n([Measures].[Issues history],\n[Transition Status].[Testing])*0.7\n+\n([Measures].[Issues history],\n[Transition Status].[Ready For Release])*0.95\n+\n([Measures].[Issues history],\n[Transition Status].[Closed])\n","format_string":"#,##0.00"},{"name":"Actual start date","dimension":"Measures","formula":"--annotations.group= S-Curve\nDateParse('2024-03-01')","format_string":"yyyy-mm-dd"},{"name":"Planned end date","dimension":"Measures","formula":"--annotations.group= S-Curve\nDateParse('2025-03-31')","format_string":"yyyy-mm-dd"},{"name":"Project End","dimension":"Measures","formula":"--annotations.group= S-Curve\nCase when\n    DateInPeriod(\n      DateAddDays([Measures].[Planned end date],0),\n      [Time].CurrentHierarchyMember)\n   Then\n     'Planned End Date'\nEnd","format_string":""},{"name":"Project Start","dimension":"Measures","formula":"--annotations.group= S-Curve\nCase when\n    DateInPeriod(\n      DateAddDays([Measures].[Actual start date],0),\n      [Time].CurrentHierarchyMember)\n   Then\n     'Actual Start Date'\nEnd","format_string":"#,##0"}]
}

I’m desperate to solve this. Here is a picture of a cute dog in the hopes you take pity on me. The need is to find the earliest date and latest date in fixversions used to filter a date based calculation ( S-Curve). I want to use these start and end dates from fixversions as the start and end of the S-Curve calculation as described in the scrubbed report above.

I think this may answer it:

Hello @guy.winterbotham ,
I love the cute dog! :dog: :bone:

You already have found a good example from the other community post; you can use this formula to get the earliest fix version start date:

Tail(
  Order(Filter(
    DescendantsSet([Fix Version].CurrentHierarchyMember,[Fix Version].[Version]),
    Not IsEmpty([Fix Version].CurrentHierarchyMember.Get('Start date'))
    AND
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember)> 0
    ), [Fix Version].CurrentHierarchyMember.Get('Start date'), BDESC
  )
).Item(0).Get('Start date')

This is to get the latest fix version release date:

Tail(
  Order(Filter(
    DescendantsSet([Fix Version].CurrentHierarchyMember,[Fix Version].[Version]),
    Not IsEmpty([Fix Version].CurrentHierarchyMember.Get('Release date'))
    AND
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember)> 0
    ), [Fix Version].CurrentHierarchyMember.Get('Release date'), BASC
  )
).Item(0).Get('Release date')

Kindly,
Gerda // support@eazybi.com

@gerda.grantina thanks for the validation. I still have one more challenge that I only saw once I applied these formulae. If plotted against Time AND multiple fixversions are selected AND in one month the issues just happened to have the fixversion with an earlier Release Date then the time based calculation I use for the S-Curve guide line uses different dates in each row. The S-Curve guideline calculation is identical to the eazyBI example for the Project Prediction example:

[Measures].[S-curve guideline]  =
--annotations.group= S-Curve
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember)
/
(1+Exp(-DateDiffDays(
  DateAddDays(
    [Measures].[earliest fix version start date],
      -- "2" assume that s-curve meets the linear prediction in the middle
    DateDiffDays([Measures].[earliest fix version start date],
      [Measures].[latest fix version release date])/2 
  ),
 -- "50" is parameter of how steep the curve will be
  [Time].CurrentHierarchyMember.StartDate)/10
))

See the different release dates in the sample below and how it impacts the S-Curve Guideline calculation. It looks like if I can get the release date at the highest level of the hierarchy then I would be set. I don’t know how to do that:

earliest fix version start date latest fix version release date S-curve guideline Time in project Today Project End Project Start Weighted Status
All Times 2024-05-01 2025-03-01 916.00 1 Today Planned End Date Actual Start Date 453.60
2024 2024-05-01 2025-03-01 0.00 1 Today Actual Start Date 453.60
Q2 2024 2024-05-01 2025-03-01 0.00 1 Actual Start Date 14.00
Q3 2024 2024-05-01 2025-03-01 0.10 1 197.70
Q4 2024 2024-05-01 2025-03-01 480.88 1 Today 453.60
Oct 2024 2024-05-01 2025-03-01 480.88 1 392.40
Nov 2024 2024-05-01 2025-01-01 914.14 1 Today 453.60
04 Nov 2024 2024-05-01 2025-01-01 914.62 1 407.90

I understand to form the timeline I would switch to, for example, All hierarchy level members->Weekly->week.

I hope this is enough information. I feel I’m a line or two away from cracking this one. Thanks in advance for your assistance!

Here is a Eurasian Lynx being adorable in the hope it will attract attention to my tiny question.
this-beautiful-eurasian-lynx