Show Trends Across Sprints

[ascii75]

Hello! I am trying to show a trendline for the Planned to Done % across sprints.

The column is [Measures].[Planned to Done %] which is calculated as: [Measures].[Cumulative Story Points resolved] / [Measures].[Sprint Story Points committed].

The Rows are sprints.

Table and line chart:

I am trying to get the trend of the Planned to Done % across all Sprints, but I’m struggling with how to do so. I have created the following Calculated Measure, [Measures].[Done % Trend]:
LinRegPoint(
[Sprint].[Board].CurrentMember,
Filter([Sprint].[Board].Members,
[Sprint].[Board].CurrentMember.Name MATCHES ‘CC Dashboards’
),
[Measures].[Planned to Done %],
[Sprint].CurrentMember.get(‘Start date’)
)

I have tried MANY variations for the LinRegPoint, all unsuccessfully. The code above is just the latest attempt. Any assistance would be GREATLY appreciated!

[malikgp]

Hi ascii75,

I use LinRegPoint function for the following graph.

The purpose of this graph is to show the rolling average story points completed by a team (blue line) and show the trend line of those data points over the past two years.

Rolling Average Story Points takes the sum story points resolved over the prior 30 days and averages it with the current date to smooth out the variations.

Cache(
  Avg(
    LastPeriods(30, [Time].CurrentHierarchyMember),
    [Measures].[Story Points Resolved (R)]
  )
)

The results of that daily smoothed calculation are the blue line. The dashed red trend line, e.g. what you’re trying to show, takes those daily calculations and draws the trend line for the prior 2 years until the present day.

Cache(
  LinRegPoint(
    DateToTimeStamp([Time].CurrentHierarchyMember.StartDate),
    Filter(
      [Time].CurrentHierarchyMember.Level.Members,
      DateBetween([Time].CurrentHierarchyMember.StartDate,
        '2 years ago','Now')
    ),
    [Measures].[Rolling Average Story Points],
    DateToTimeStamp([Time].CurrentHierarchyMember.StartDate)
  )
)

Looking at your function, it seems like you would want to get the start date of the sprint within the LinRegPoint function.

I can’t test it but it seems like it would be something like:

Cache(
	LinRegPoint(
		DateToTimeStamp([Sprint].[Board].CurrentMember.get("Start date")),
		Filter(
			[Sprint].[Board].Members,
			[Sprint].[Board].CurrentMember.Name MATCHES 'CC Dashboards'
		),
		[Measures].[Planned to Done %],
		DateToTimeStamp([Sprint].[Board].CurrentMember.get("Start date"))
	)
)

Give that a shot, hope this helps!

– Malik Graves-Pryor

[daina.tupule]

Malik, Great suggestion!

A function LinRegPoint is a correct one to represent the trend.

LinRegPoint(Numeric expression output x, Set expression, Numeric expression y, Numeric expression x)

It requires numeric values on X axis. You somehow need to get numeric ordered value for any Sprint on rows. You can use the Sprint start date and get Timestamp (numeric) value based on it.

DateToTimeStamp([Sprint].CurrentMember.get("Start date"))

Here are some pointers you should take into account. The formula will work over Sprint with Start date. You can’t populate it over future Sprints.

You would like to define a set of Sprints as well. Here is the formula that will work within one Board.

CASE WHEN 
NOT IsEmpty([Sprint].CurrentMember.get("Start date"))
THEN
   LinRegPoint(
      DateToTimestamp([Sprint].CurrentMember.get("Start date")),
      Descendants([Sprint].Parent, [Sprint].[Sprint]),
      [Measures].[Planned to Done %],,
      DateToTimestamp([Sprint].CurrentMember.get("Start date"))
   )
END

I used Sprint dimension both on Pages and Rows and select one Board on Pages and used Sprint level on Rows. The function will work for any Board selected on Pages.

Here is another example of Sprint trend line over several Boards in our demo account:
https://eazybi.com/accounts/1000/cubes/Issues/reports/68000-story-points-burn-down-in-selected-epic

It calculates Sprint rank instead of using Sprint start date. The example is with Epics, but you can use any other pages selections and the report still should work.

support@eazybi.com