Different results with Sprint Story Points Committed - custom calculation vs manually instantiated case

gsipos · August 21, 2024, 10:57am

Hello,

Context:

I need to sum the committed story points of the sprints started in every two weeks within a grace period.
The [Time] dimension has the 2-week-based configuration, which starts on every other Wednesdays.
The sprints should start on Weds, however, they may start with one day earlier or later.
The report, which uses this information, has only the [Project] dimension on the rows. That is, no [Time] dimension is used.
I created the custom calculation called [Measures].[CommittedSPs], which has a generic implementation for calculating the committed story points of sprints started with +/- 1 day difference compared to the Wed-s, which are the StartDate of each 2-week-long period.
I need to see history on this, therefore, I am using the Lag() function too.

[Measures].[CommittedSPs]
Sum(
  Filter(
    [Sprint].[Sprint].Members,
    DateBetween(
      DateWithoutTime( [Sprint].CurrentMember.Get('Activated date')    ),
      DateWithoutTime( DateAddDays( [Time].CurrentHierarchyMember.StartDate, -1 ) ),
      DateWithoutTime( DateAddDays( [Time].CurrentHierarchyMember.StartDate,  1 ) )
    )
  )
  ,
  [Measures].[Sprint Story Points committed]
)

The custom calculated measure would be instantiated with a specific time in the report. e.g.

  (
    [Measures].[CommittedSPs],
    [Time.2 Weeks].[2 Weeks].CurrentDateMember.Lag(5)
  )

I am using SparkLineData with column mode to visualise the committed story points over time, therefore, I am using it the way below:

SparklineData(
   LastPeriods(6, [Time.2 Weeks].[2 Weeks].CurrentDateMember.Lag(1))
  ,
  [Measures].[CommittedSPs]
)

The issue

The custom calculation [Measures].[CommittedSP] does not return value for a case, when the Sprint was started a day earlier compared to the Wed-s. However, the ‘-1’ condition should cover that case.
The custom calculation returns correct data for other sprints. SparkLine has columns with the right values.
If I use the code below, which basically selects explicitly a specific time, then the [Measures].[Sprint Story Points committed] returns a value.

    Sum(
      Filter(
        [Sprint].[Sprint].Members,
        DateBetween(
          DateWithoutTime( [Sprint].CurrentMember.Get('Activated date')         ),
          DateAddDays( [Time.2 Weeks].[2 Weeks].CurrentDateMember.Lag(5).StartDate, -1 ),
          DateAddDays( [Time.2 Weeks].[2 Weeks].CurrentDateMember.Lag(5).StartDate,  1 )
        )
      )
      ,
      [Measures].[Sprint Story Points committed]
    )

I have already debugged the sub parts, meaning

The right date is selected
The right date period is calculated
The right sprints are identified

I ran out of ideas of why I see the difference results between the Custom Calculation and the manually instantiated one. However, they are using the same logic to filter for the sprints activated in the grace period (+/- 1 day)

Thank you for your help in advance,
Gyula

oskars.laganovskis · August 23, 2024, 8:50am

Hi @gsipos,

Welcome to the eazyBI community!

This is a really good one!

When you are setting explicit Time dimension member within the debugging expression - it only applies to the filtering part.
However, when applied to the whole expression - it also applies to the core measure - “[Measures].[Sprint Story Points committed]”.

In the case of “early sprints” - while the filtering part provides the grace period of +/- one day, the measure part is strict and only looks for the story points committed within the current period. It finds none, as the sprint was started early.

You can fix this by resetting the Time dimension on the measure part as the filtering for the relevance to the Time dimension member is already done on the sprints with a grace of +/-1 day.

The updated expression might be as follows.

Sum(
  Filter(
    [Sprint].[Sprint].Members,
    DateBetween(
      DateWithoutTime( [Sprint].CurrentMember.Get('Activated date')    ),
      DateWithoutTime( DateAddDays( [Time].CurrentHierarchyMember.StartDate, -1 ) ),
      DateWithoutTime( DateAddDays( [Time].CurrentHierarchyMember.StartDate,  1 ) )
    )
  ),
  ([Measures].[Sprint Story Points committed],
--resetting the Time dimension as filtering already applied for sprints
   [Time].CurrentHierarchy.DefaultMember)
)

This should work for both on-time and early sprints.

Regards,
Oskars / support@eazyBI.com

gsipos · August 24, 2024, 9:29am

Hi @oskars.laganovskis,

Thank you for your answer.
Yes, that [Time] hierarchy “reset” worked, and now I get the information from the “early sprints” as well.

I just partially understood this Time hierarchy reset as my understanding was for using the Sum() with a Filter() is that

The Filter() gives me back the set of the sprints which were “activated” inside the grace period.
The Sum() applies the [Measure].[Sprint Story Points committed] for each element of the set regardless of the time.

(
  [Sprint].[BoardA].[Sprint_AX],
  [Measures].[Sprint Story Points committed]
)
+
(
  [Sprint].[BoardB].[Sprint_BY],
  [Measures].[Sprint Story Points committed]
)
+
...
-- and so on

Based on your description, it seems, the [Time] dimension is added to the tuple, too. Am I correct?

Also, I think the DateWithoutTime() on these lines is unnecessary. Thus, I can save some resources when filtering the sprints.

DateWithoutTime( DateAddDays( [Time].CurrentHierarchyMember.StartDate, -1 ) ),
DateWithoutTime( DateAddDays( [Time].CurrentHierarchyMember.StartDate,  1 ) )

→

DateAddDays( [Time].CurrentHierarchyMember.StartDate, -1 ),
DateAddDays( [Time].CurrentHierarchyMember.StartDate,  1 ),

Concerning performance
I have to use this filtered set of Sprints in additional calculations (committed, added, removed, completed for story points and issues). Can I somehow store the set of Sprints and reference it instead of applying the Filter() on every calculation?

I know about the Cache() function. I can also define a custom calculated member in the [Sprint] dimension. However, mixing different dimensions (Sprint and Time) is not recommended.

I need to work on a massive amount of Jira projects and Sprints. Also, I need to provide a historical view of the last 6 periods to see the trends.

(As I mentioned in my first post, the report, what I put together has only Projects on rows (multiple categories and with many projects) and no time dimensions. Therefore, I have to apply the [Time] dimension separately.)

Thank you again,
Gyula

oskars.laganovskis · August 26, 2024, 6:22am

Hi @gsipos,

When using the SparklineData() function over LastPeriods(), you add the Time context to the measure. That context is automatically applied to the core measure within unless overridden.

You are right - you can drop the DateWithoutTime from lines containing references to

[Time].CurrentHierarchyMember.StartDate

as the .StartDate on Time dimension is always returned as midnight.
That does not make a significant impact, therefore was not addressed initially.

You are right on the performance matter - filtering all sprints for the sparklines might consume lots of resources.

You might reduce the dataset for Sprints filtering by creating a calculated member “Sprints of last 6 periods” with the following expression.

Aggregate(
 Filter(
  [Sprint].[Sprint].Members,
    DateBetween(
      DateWithoutTime( [Sprint].CurrentMember.Get('Activated date')    ),
      DateAddDays( [Time.2 Weeks].[2 Weeks].CurrentDateMember.lag(7).StartDate, -1),
      DateAddDays( [Time.2 Weeks].[2 Weeks].CurrentDateMember.StartDate,  1 )
    )))

In this case, the Time dimension is only temporarily used as absolute figures—the start dates of specific members.

Then, you might replace the [Sprint].[Sprint].Members within the “CommittedSPs” with a subset of the applicable calculated member in the following way.

Sum(
  Filter(
--subset of selected sprints at the sprint level
    DescendantsSet(
     [Sprint].CurrentHierarchyMember,
     [Sprint].[Sprint]),
    DateBetween(
     DateWithoutTime( [Sprint].CurrentMember.Get('Activated date')    ),
     DateAddDays( [Time].CurrentHierarchyMember.StartDate, -1 ),
     DateAddDays( [Time].CurrentHierarchyMember.StartDate,  1 )
    )
  ),
  ([Measures].[Sprint Story Points committed],
--resetting the Time dimension as filtering already applied for sprints
   [Time].CurrentHierarchy.DefaultMember)
)

This will lead to improved performance as the subset of sprints within the last periods is radically smaller than the total set of sprints within the data cube.

Regards,
Oskars / support@eazyBI.com

gsipos · August 28, 2024, 8:46am

Hi @oskars.laganovskis,
Thanks again for your answers.

OK, I understand now, why the [Time] dimension is applied on the Core measure with the LastPeriods().

//
I created the suggested calculated member under the [Sprint] dimension. Also, updated my custom calculated measure per your example. Thanks for those, and they make sense.

//
To utilise the smaller set of Sprints, either I have to add this calculated set as a Page filter on the report or use it in a calculation explicitly. The reason is that, I have additional calculations, e.g. the overall blocked issues ratio and cycletime, which should not be affected by the smaller set of Sprints.
Therefore, I need to add the Sprint set on each calculation to improve their performance, e.g.

SparklineData(
  LastPeriods( 6, [Time.2 Weeks].[2 Weeks].CurrentDateMember.Lag(1) )
  ,
  (
    [Measures].[Sprint Story Points completed w/ grace period]
    ,
    [Sprint].[Sprints of the last 6 periods] -- adding the smaller set of Sprints
  )
)

I assume, by using the form of
[Sprint].[Sprint].Member or the other version

DescendantsSet(
  [Sprint].CurrentHierarchyMember,
  [Sprint].[Sprint]
)

will not make any difference as the set of Sprints explicitly defined. I understand, how I can utilise this DescendantsSet() approach though.

Anyway, thank you for your answers, these helped to solve my issues.

Regards,
Gyula