12 Month Lookback of Open Defects (Snapshots EOM)

Questions & Answers
1

Hi, looking to create a chart that has 12 monthly lookback showing a line with 12 values - open defects at the end of each 12 months.

I have something working but its including closed, which I have defined what that is at the project level.

Any ideas ? Could I possible create an outer loop below filtering Status = Done, Rejected, Duplicate (these are my closed status).

CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ THEN
Cache(
NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
[Measures].[Issues created]
- [Measures].[Issues closed]
))
+ [Measures].[Issues created]
- [Measures].[Issues closed]
)
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN NonZero([Measures].[Issues due])
ELSE
– optimized formula for drill through Issue
NonZero(IIF(
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember),
([Time].CurrentHierarchy.DefaultMember,
[Measures].[Issues created]),
0
))
END

2

Hi @MichaelG,

I use a slightly different method to show my open issues by EOM.
First, I create a new member in my Transition Status dimension. In my case, I wanted all status except “closed”. In your case, you could filter out the other statuses too.

Open_list =

Aggregate( Except([Transition Status].[Transition Status].Members, 
	{ [Transition Status].[Closed],
    [Transition Status].[other_status_to_exclude]
  } 	--List members to exclude
	))

With Time dimension in Row with Months hierarchy selected, I select “Issue History” in the predefined measures and add my new status under.
It’s also possible to create a new measure

([Measures].[Issues history],[Transition Status].[Open_list])

I hope this helps!

Marilou

3

Hi @Marilou

Thank you so much, this helped and now I have what I need.

Very grateful,
Michael.