Sum of Epic story points transitioned into a State by time (Qtr)

Questions & Answers
1 
CASE 
WHEN ([Measures].[Transitions to status issues count],[Transition Status].[Something]) > 0
THEN [Measures].[Story Points created].value
ELSE
[Measures].[Issues created].Value
END

This works as expected until I add the Time Dimension as a row (by Quarter) and then issues that do have story points are evaluating like they don’t. If I do “All Time” It works as expected as well.

Another oddity is that the quarters the Epic is put into appears to be Create dates of the Epic not the date they transitioned into the state I am looking for.

Total is correct in each case

image
image174×686 5.41 KB

image
image261×659 4.05 KB

image
image183×668 2.71 KB

This has me pretty stumped.

Thanks!

2

Hi @JiraSauce ,
The measures “Story Points created” and “Issues created” are linked to Time dimension by issue creation date, but “Transitions to status issues count” by the date when the transition was done to particular status (“Something”) thus the result changes when you use Time dimension in your report.

Do you want to return the measure “Story Points created” for only those issues that have transitioned to status “Something” and do it by the transition date? And if the transition hasn’t happened then use measure “Issues created” and display the result by issue creation date?
If you use “Issue” dimension in Rows, then you can use this formula where a tuple with time default member will ignore the creation date for the first measure “Story Points created”, but measure “Issues created” will still be linked by the issue created date:

  CASE WHEN([Measures].[Transitions to status issues count],[Transition Status].[In Review]) > 0
  THEN 
    Val(([Measures].[Story Points created], [Time].CurrentHierarchy.DefaultMember))
  ELSE
    Val([Measures].[Issues created])
  END

And if you would like to use this logic without “Issue” dimension then you need to filter() the issues by one or another measure and then sum() the result depending on which condition is true:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    ([Measures].[Transitions to status issues count],[Transition Status].[Something]) > 0
    OR
    [Measures].[Issues created]>0
  ),
  CASE WHEN([Measures].[Transitions to status issues count],[Transition Status].[Something]) > 0
  THEN 
    val(([Measures].[Story Points created], [Time].CurrentHierarchy.DefaultMember))
  ELSE
    val([Measures].[Issues created])
  END
)

best,
Gerda // support@eazyBI.com

3

Thank you @gerda.grantina

That found all the story points that were missing! but there are some that double counted and I’m not sure why because they only have one transition into state “something”.

image
image369×829 10.4 KB

If I remove the issue dimension then they don’t seem to add up (78 vs 72)

image

4

Hi @JiraSauce you can check the data by drill through issues option to see where the difference comes from.
image

Could be that one issue transitioned multiple times into a “Something” state?

Kind regards,

5

Yes, I have done that @gerda.grantina. All the issues only have one transition to “Something” any other ideas why there may be duplicates?

6

Hi @JiraSauce,
Did you notice which issues are in double in your report by using “Drill through issues” option?
You can send an email to eazyBI support with more details to investigate why some issues are counted twice, reference the mail to this community question.

Gerda // support@eazyBI.com

7

Ok I figured it out.

The problem was the ELSE. It was also including the the quarters when there is no transition to “Something”. By changing ELSE to 0 and adding an if statement into THEN

NonZero(
  Sum(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      ([Measures].[Transitions to status issues count],[Transition Status].[Something]) > 0
      OR
      [Measures].[Issues created]>0
    ),
    CASE WHEN([Measures].[Transitions to status issues count],[Transition Status].[Something]) > 0
    THEN 
      IIf(val(([Measures].[Story Points created], [Time].CurrentHierarchy.DefaultMember))>0,
        val(([Measures].[Story Points created], [Time].CurrentHierarchy.DefaultMember)),
        1)
    ELSE
      0
    END
  )
)

Thank you so much for your help! @gerda.grantina

1 Like