Sum Hours Spent in the Last Two Sprints

Questions & Answers
, , , ,
1

Hi everyone,

I am new to eazyBI MDX–can someone assist how to write an MDX formula to sum up the Hours Spent from the last two completed sprints per project?

For ex:

ABC project
Sprint 0 ABC: 10 hours spent
Sprint 1 ABC: 10 hours spent

DEF project
Sprint 0 DEF: 5 hours spent
Sprint 1 DEF: 5 hours spent

There should be a bar under ABC project with a total of 20 hours spent and another bar with a total of 10 hours spent for DEF project.

Thank you, much appreciated for the help!

2

Hi @Keiko

Sure thing,

You can create a new member “Last 2 closed sprints” in the Sprint dimension with a formula like this:

Aggregate(
  Generate(
    Filter(
      [Sprint].[Board].Members,
      [Sprint].CurrentMember.Name NOT MATCHES "\(no sprint\)|\(multiple boards\)"
    ),
    Tail(
      Filter(
        [Sprint].CurrentMember.Children,
        [Sprint].CurrentMember.GetBoolean("Closed")
      ),
      2
    )
  )
)

Then you can use the Project dimension in Rows and “Hours spent” measure in the Measures dimension:

last-2-closed-sprints
last-2-closed-sprints2014×1374 267 KB

​Best regards,
​Nauris