We have a source data in SharePoint list of all reported issues(incidents) in our department. The source data contains a column:
Issue Status – Received, Open, Resolved, Closed values and the date columns (Issue date, Resolved date, Closed date) auto set based on their status change. The definitions of each statuses are as follows:
Issue Status:
1) Received: An issue is considered received if the Issue Date occurs within the specified time period.
2) Open: An issue is considered open if the Issue Date occurs prior to the end of the specified time period, and the Closed Date is after the end of the specified time period.
3) Resolved: An issue is considered resolved if the Issue Date occurs prior to the end of the specified time period, and the Resolved Date is within the end of the specified time period.
4) Closed: An issue is considered closed if the Issue Date occurs prior to the end of the specified time period; the Closed Date is within the end of the specified time period; and the Issue Status is equal to “Closed”.
I have a metrics report (bar chart) built with slicer selection for Weekly, Quarterly, Monthly and Yearly and Issue type. The challenge I am facing is :
Expected scenario: If an issue is received in week 1 then the count for "Received" status increments by 1 for that particular week and if the issue record remains in same status for next 3 weeks, then it will continue to show in same status for 3 more weeks, which is the expected correct.
Current situation(Problem statement):
The problem is when the status is changed to Open or Resolved for that particular issue item in week 4, the “Received” count of week 1 also goes down by “-1” which should NOT occur. It should always maintain the previous count of “Received” status for the weeks 1 – 3.
This behavior is applicable to other statuses too. When an issue is resolved in week 3 and was open until then, the “open” count should not change for week 1 & 2.
Example with date columns: If Issue Date is 3-Jul-17, Resolved Date is 10-Jul-17 and Closed Date is 17-Jul-17, the following will show for the specified date ranges.
Date Range Selected | Open | Resolved | Closed |
24-Jun-17 Weekly | 0 | 0 | 0 |
1-Jul-17 Weekly | 1 | 0 | 0 |
8-Jul-17 Weekly | 0 | 1 | 0 |
15-Jul-17 Weekly | 0 | 0 | 1 |
I would probably have to create the right calculated fields and use them to show in the metrics. Any help is much appreciated.
