Get Issues created in past months and resolved in the current month

Questions & Answers
, ,
1

Hello! I would like to know how I could create a measure to be able to add the issues that were created in previous months and resolved in the current month

try something like that but it doesn’t work
image

Thanks!

2

Hi, @Emiliano_Romero
​
​To create a measure that counts issues created in previous months and resolved in the current month, please consider using Descendants. Read more about them here: Descendants
​
For example, this formula takes the month from the current date ([Time].[Month].CurrentDateMember) and looks for the previous month based on that. Counts Issues resolved.
​

NonZero(Sum(
  Filter(
  Descendants([Issue].CurrentMember,[Issue].[Issue]),
  DateInPeriod( 
    [Measures].[Issue resolution date],
    [Time].[Month].CurrentDateMember)
  AND
  DateInPeriod( 
    [Measures].[Issue created date],
    [Time].[Month].CurrentDateMember.PrevMember )
  ),
[Measures].[Issues resolved]
))

​
​Also, there is the option if you have Time dimension in Pages and want to see not only the current month but choose the month in the past. For this formula to work, always choose the month level on your Time dimension in Pages (it won’t work correctly with the level date, year or quarter).
​

NonZero(Sum(
  Filter(
  Descendants([Issue].CurrentMember,[Issue].[Issue]),
  DateInPeriod( 
    [Measures].[Issue resolution date],
    [Time].CurrentHierarchyMember)
  AND
  DateInPeriod( 
    [Measures].[Issue created date],
    [Time].CurrentHierarchyMember.PrevMember )
  ),
[Measures].[Issues resolved]
))

​
Kind regards,
Ilze​
​

3

Thanks for the reply. It is useful but I would like to know if it is possible to take into account all the previous months. For example, if you create an issue in January 2023 and I solved it in April 2023, you should add

I tried this but it doesn’t work

NonZero(Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember)
AND
DateInPeriod(
[Measures].[Issue created date],
PreviousPeriods([Time].CurrentHierarchyMember))
),
[Measures].[Issues resolved]
))

4

Hi, @Emiliano_Romero

If you want to see all the Issues, that are created before current month, but resolved in current month, use this formula:

NonZero(Sum(
  Filter(
  Descendants([Issue].CurrentMember,[Issue].[Issue]),

  DateInPeriod( 
    [Measures].[Issue resolution date],
    [Time].[Month].CurrentDateMember)
  
  AND
  
  NOT DateInPeriod( 
    [Measures].[Issue created date],
    [Time].[Month].CurrentDateMember)
  ),
[Measures].[Issues resolved]
))

Kind regards,
Ilze

5

Hello Again!, how can adapt this code to works with page time level year o quarter? only work with months.

NonZero(Sum(
  Filter(
  Descendants([Issue].CurrentMember,[Issue].[Issue]),

  DateInPeriod( 
    [Measures].[Issue resolution date],
    [Time].[Month].CurrentDateMember)
  
  AND
  
  NOT DateInPeriod( 
    [Measures].[Issue created date],
    [Time].[Month].CurrentDateMember)
  ),
[Measures].[Issues resolved]
))

Thanks