Hi @Stijn,
Since you are filtering issues by their properties - the filter for creation date always returns the set of all issues created between 13 Oct 2022 and today.
The same set of issues is returned for any date in the report context.
If you want your calculated measures to relate to the selected context - like report pages or displayed dimensions - you might filter by the actual imported measures like issues created, issues resolved, etc. in the calculations and conditions.
The point to consider is if you want to allocate issues to the Time periods when these issues were created, when they have been transitioned to “Ready for Handover” of when they were closed/completed./resolved.
The current version of the first expression displays the issues that have transitioned to “Ready for Handover” in the displayed member of the Time dimension regardless of issue creation date as long as the issue was created after 13 Oct 2022. This might lead to the same issue being reported multiple times if it gets transitioned to Ready for Handover multiple times.
The current version of the second expression returns all issues that were created after 13 Oct 2022 that have not transitioned to “Ready for Handover” in the displayed Time dimension member, regardless of their closure or resolution date and regardless of the relation of their creation date to the displayed Time dimension member.
The issues that have NOT been transitioned to “Ready for Handover”, have to be grouped by their creation or closure date.
However, the name of the measure “…closed but not delivered” hints that you might be looking for the issue closure date as the date to group the issues.
If you want only to see the issues created within the displayed Time dimension member AND after 13 Oct 2022 - you need to add an extra condition - the issue was created in the displayed period.
The condition might be as follows.
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember)
If you want to see the issues that were resolved within the selected period, you might use the following condition.
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember)
Depending on how you define issue closing - it might be issue resolution date or issue closed date. If you use the transitions to specific statuses instead of a resolution to mark issue completion - you need to set up these statuses in data import settings. Please read more about that here - Issues closed measure.
The updated expressions based on more detailed requirements might be as follows.
The number of issues that were created after 13 Oct 2022, resolved within the displayed period, that have one of the two issue types and have gone to Ready for Handover at any point in time .
NonZero(Count(
Filter(
--set of issues
DescendantsSet(
[Issue].CurrentMember,
[Issue].[Issue]),
--condition 1 created after 13 Oct 2022
DateBetween(
[Measures].[Issue created date],
'13 Oct 2022',
'today'
)
AND
--condition 2 transitioned to Ready for Handover anytime in history
(
([Measures].[Transitions to status issues count],
[Transition Status].[Ready for Handover],
--resetting the Time dimension
[Time].CurrentHierarchy.DefaultMember)
>0) --checks if issue went to Ready for Handover
AND
--condition 3 - current issue type one of not-converted
(
[Measures].[Issue type] = "Technical Epic"
OR
[Measures].[Issue type] = "Technical Story"
)
AND
--condition 4 - linked to Time dimension by resolution date
--also filters for other context - other dimensions
[Measures].[Issues resolved]>0
)
))
The number of issues closed within the displayed period , that were created after 13 Oct 2022, that have one of the two issue types and have not gone to Ready for Handover at any point in time .
NonZero(Count(
Filter(
--set of issues
DescendantsSet(
[Issue].CurrentMember,
[Issue].[Issue]),
--condition 1 created after 13 Oct 2022
DateBetween(
[Measures].[Issue created date],
'13 Oct 2022',
'today'
)
AND
--condition 2 - NOT transitioned to Ready for Handover anytime in history
NOT(
([Measures].[Transitions to status issues count],
[Transition Status].[Ready for Handover],
--resetting the Time dimension
[Time].CurrentHierarchy.DefaultMember)
>0)
AND
--condition 3 - current issue type one of not-converted
(
[Measures].[Issue type] = "Technical Epic"
OR
[Measures].[Issue type] = "Technical Story"
)
AND
--condition 4 - is currently in the Closed status
([Measures].[Issue status] = "Closed")
AND
--condition 5 - linked to Time dimension by closure date
--also filters for other context - other dimensions
[Measures].[Issues closed]>0
)
))
When multiple dates are used to identify the issues - it is important to clearly define the relationship of each used date field to the report context to create a proper calculation.
Once the primary expression returns the proper result - you might look for further performance improvements by re-arranging or nesting the conditions and reducing the dataset subjected to the conditions.
Regards,
Oskars / support@eazyBI.com
