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"}]
}
