Summing results on another level

daina.tupule · June 19, 2017, 10:53am

Hi Jacques

DateDiffDays is the function for calculating the duration between Version Start date and Version End date. Then you would like to use this formula over the set of Fix versions for a particular project to count Product duration.

The formula below calculates the sum of version durations for all versions of the particular selected project “Product duration”:

Sum(
  Filter(
    Descendants([Fix Version].CurrentMember, [Fix Version].[Version]),
    [Measures].[Issues created] > 0
  ),
  DateDiffdays(
    [Measures].[Version start date],
    [Measures].[Version release date]
  )
)

Then you can use the calculated measure “Product duration” to calculate an average duration of all projects:

Cache(Avg(
  [Project].[Project].Members,
  [Measures].[Product duration]
))

Then you can use both measures on your report over projects.