Hello community,
I have a doubt that it’s around my mind and I can’t resolve.
I need to know if it is possible to get the date(s) that a issue had in the past and create metric with all of them.
I see that when a field is changed, the issue gathers this change and you can check it in the “history” tab but I don’t know if we can work with all these changes.
Why? I need to create a metric in EazyBI where the results for each period hierarchy set shows the issues overdue in this particular period regardless if the due date changed later.
Example : We are on 2019/01/03 and we hace the following report:
2019/01/01 = 5 issues (2 issues with due date set on 2018/12/30 and the remaining 3 in 2019/01/01)
2019/01/02 = 10 issues (5 issues with due date set on 2019/01/02 and the 5 overdue issues aforementioned)
Well, we are now on 2019/02/01 and for any reason I modify the 5 overdue issues on 2019/01/02 to 2019/03/01, I need that the result for “2019/01/02” remains 10 because in this day was 10 overdue issues and I have reported that to my boss in the past.
Is it possible?
I have using the following MDX code to get the overdue issues in the period set but they update when the due date is modified.
Count(Filter(
Generate(
[Time].CurrentHierarchy.DefaultMember,
Cache(Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issues with due date] > 0 AND
DateCompare([Issue].CurrentMember.get(‘Due date’),
Now()) < 0
))
),
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember) AND
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Due date’),
[Time].CurrentHierarchyMember)
))