Straight line between 2 points to predict future Maturity( Story points)

Questions & Answers
, ,
1

I would like to create a calculate defined member in the measures dimension on that does the following:

A straight line that goes through zero on the first date that the measure “Cumulative Maturity Forecast” is greater than 0, then goes through the value of todays “Cumulative Maturity Achieved” measure.
We are not using versions and although our tasks with the Maturity (Story Points) are in epics we do not use the epic for dates.

See picture, I would like it to draw the red line currently on there.
Is this possible?
I have looked at the demo prediction ones and tried editing them. cant seem to get any to work as they are relying on versions or epics for start dates.

Screenshot 2024-05-03 170932 with red line
Screenshot 2024-05-03 170932 with red line1641×322 23.4 KB

2

Hi @HLL,

You can write the calcaution for the guideline. The main components of each guideline are:

  • First date (or start date) from which to take the first measurement
  • Second date from which to take the second measurement
  • and the average pace (of Maturity or Story Points in your case) for each day.

First date
In your case, the first date is when the measure has a positive value. The expression for calculated measure (in Measures) could look like this:

Head(
  Filter(
    --iterate through a set of Dal level members in the Time dimension
    [Time].[Day].Members,
    --check when were some values for maturity measurement
    [Measures].[Maturity Achieved] > 0
  ),1
--show the data of the first Day member with data
).Item(0).StartDate

Second date
In your case, the second date is today, or I would recommend using the date when data were refreshed (usually, it is the same date).
The expression for calculated measure (in Measures) could look like this:

--return the date of the last data import when data were refreshed
[Time].[Day].CurrentDateMember.StartDate

The average pace
Use the total amount of accumulated maturity and divide it by day count between first and second date. TTo get accumulated amount of maturity, you can use the maturity measure and show its value of all times (ignore the date on report rows).
The expression for pace calculation might look like this:

--accumulated maturity , 
([Measures].[Maturity Achieved],
[Time].CurrentHierarchy.DefaultMember)
/
--divided by total count of days between first and second date
DateDiffWorkdays(
  [Measures].[First date],
  [Measures].[Second date]
)

The guideline
Combining all three elements together, the calculated measure (in Measures) for the guideline might look like this:

CASE WHEN --draw line from First date and project it in future
  DateCompare(
    [Time].CurrentHierarchyMember.StartDate,
    [Measures].[First date]
  ) >= 0 OR
  DateInPeriod(
    [Measures].[First date],
    [Time].CurrentHierarchyMember
  )
THEN
  --the maturity pace per day
  --accumulated maturity  
  ([Measures].[Maturity Achieved],
  [Time].CurrentHierarchy.DefaultMember)
  /
  --divided by total count of days between first and second date
  DateDiffWorkdays(
    [Measures].[First date],
    [Measures].[Second date]
  )
  * 
  --multiplied by days from first date to see the projection on each day
  DateDiffWorkdays(
    [Measures].[First date],
    [Time].CurrentHierarchyMember.StartDate
  )
END

More details on calcauted measures are described in the documentation: Calculated measures

See also those Community posts on the main concepts how to build guidelines between two dates:

Best,
Zane / support@eazyBI.com

1 Like