Summing results on another level

Questions & Answers
1

Hi there,

I’m have a calculated measure “Version duration” that calculates the days between a Version’s start date and Version’s end date. Formula for this measure is:
DateDiffDays([Measures].[Version release date],[Measures].[Release Date])

I would need to have a table where I have “Projects” as rows and as columns the measures:

  1. “Product duration” = Sum(Version Duration) for the entire project.
  2. “Average duration”=Avg(Product duration)

The result I need is the following table/chart:

image.png1116×682 12.2 KB

I’m struggling to get both measures. When doing it in SQL I would have something like SUM([product duration]) GROUP BY [project]

Any help is greatly appreciated!

Thanks,
Jacques.

2

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.

project version duration.png1600×914 75.5 KB

3

Thanks @daina.tupule! This did the trick! :ok_hand: