cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gudasrinath
Regular Visitor

NEED HELP - Display Weekly/Monthly/Quarterly/Yearly count of records based on their status change

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.

 

1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: NEED HELP - Display Weekly/Monthly/Quarterly/Yearly count of records based on their status chang

@gudasrinath,

You can follow the suggestion in the similar thread below to calculate open/resolved/closed ticket.

https://community.powerbi.com/t5/Desktop/dax-how-to-count-open-tickets/td-p/100614

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors