Hi,
I am trying to create a report that would list upcoming issue resolution target dates (future dates) per section, grouped on a weekly bases.
This is what I have done:
And here are more details of the specifics of what I did to create the above report
Week 1
Aggregate(
[Time.Weekly].[Week].CurrentDateMember
)
Week 2 and subsequent (For subsequent weeks, I just added .NextMember)
Aggregate(
[Time.Weekly].[Week].CurrentDateMember.NextMember
)
Issues Estimated to be resolved
SUM(
Filter(
– iterate through set of issues
Descendants([Incident].CurrentHierarchyMember,[Incident].[Incident]),
– apply filter criteria to each issue
AnyDateInPeriod(
[Measures].[Incident Estimated Target Date],
[Time].CurrentHierarchyMember
)
),
– numeric expression - sum of relevant issues
[Measures].[Outstanding issues]
)
The report that I have generated does not accurately reflect the intended result.
I did notice that Week 1, if expanded, lists all the days in that week correctly, however, Week 2 and subsequent are missing some days. As an example, Week 2 is missing day 7 and 8. Please can you help me out on how to list all weekdays in a week ?
With the report not correct, I decided to try creating a list of currently created issues, grouped by week and the result seems accurate. I used the following similar calculated member but used Incident Date Created instead of Incident Estimated Target Date
SUM(
Filter(
– iterate through set of issues
Descendants([Incident].CurrentHierarchyMember,[Incident].[Incident]),
– apply filter criteria to each issue
AnyDateInPeriod(
[Measures].[Incident Date Created],
[Time].CurrentHierarchyMember
)
),
– numeric expression - sum of relevant issues
[Measures].[Outstanding issues]
)
Please can you help me understand why when using a future date (Incident Estimated Target Date) I am not getting the correct result? Note that both Incident Date Created and Incident Estimated Target Dates are in the same date format
Thanks in advance for looking into this issue and let me know if you require any more information