Open & Closed Tickets by status by month,Qtr,Year in Stacked Chart

[sreeni]

HI Team, 

I'm developing report where I want to analyze the open/created tickets(count) versus closed tickets(count) by ticket status by month,Qtr,Year .

Today I can filter (closed tickets or opened tickets) in my tickets system by the date using two separated measures. I'm not able to get tickets which have the same date/period of "Creation_Date" and "LatestUpdated_Date" according the calendar dim table.

Created Tickets : Using Active join enabled between creation_date and calendar dim table.

Closed Tickets: Here is my problem. Not able to figureout the no.of tickets in period based on the inactive join between LastUpdated_Date and calendar dim table. Even tried USERELATIONSHIP, but no luck. Please help.

Tickets_Details Table:

Ticket Number	CREATION_DATE	LastUpdated_Date	Resolution Status
66527	1/20/2019 0:00	1/20/2019 0:00	DRAFT
66528	1/20/2019 0:00	3/20/2019 0:00	PENDING APPROVAL
66529	1/20/2019 0:00	1/20/2019 0:00	PENDING APPROVAL
66530	1/20/2019 0:00	1/20/2019 0:00	UNDER REVIEW
66531	1/20/2019 0:00	1/21/2019 0:00	UNDER REVIEW
66532	1/20/2019 0:00	3/20/2019 0:00	DRAFT
66533	1/20/2019 0:00	1/21/2019 0:00	IMPLEMENTED
66534	1/20/2019 0:00	1/20/2019 0:00	UNDER REVIEW
66535	1/20/2019 0:00	2/21/2019 0:00	UNDER REVIEW
66536	1/20/2019 0:00	1/21/2019 0:00	UNDER REVIEW
66537	1/20/2019 0:00	1/20/2019 0:00	DRAFT
66538	1/20/2019 0:00	1/20/2019 0:00	DRAFT
66539	1/20/2019 0:00	1/20/2019 0:00	SCREEN
66540	1/20/2019 0:00	1/20/2019 0:00	UNDER REVIEW
66541	1/20/2019 0:00	1/20/2019 0:00	UNDER REVIEW
66542	1/20/2019 0:00	1/20/2019 0:00	DRAFT
66543	1/21/2019 0:00	1/21/2019 0:00	UNDER REVIEW
66544	1/21/2019 0:00	1/21/2019 0:00	UNDER REVIEW
66545	1/21/2019 0:00	3/28/2019 0:00	UNDER REVIEW
66546	1/21/2019 0:00	1/21/2019 0:00	UNDER REVIEW
66547	1/21/2019 0:00	1/21/2019 0:00	DRAFT
66548	1/21/2019 0:00	2/21/2019 0:00	WITHDRAWN
66549	1/21/2019 0:00	1/21/2019 0:00	DRAFT
66550	1/21/2019 0:00	1/21/2019 0:00	DRAFT
66551	1/21/2019 0:00	1/21/2019 0:00	SCREEN
66552	1/21/2019 0:00	1/21/2019 0:00	UNDER REVIEW

Regards

SRRY

[avanderschilden]

Hello,

Did you use USERELATIONSHIP in the following way;

Measure =
CALCULATE (
    COUNTROWS ( Tickets_Details ),
    USERELATIONSHIP ( 'Calendar'[Date], Tickets_Details[LastUpdated_Date] )
)

It should work...

[sreeni]

Hi, Thanks for the reply.  my problem here is that when I add Resolution Status, the no.of tickets created showing incorrect as data is spliting based on resolution status.

Basicaly, I want see how many tickets created,closed in period(month,Qtr,Year). Staus is also needed in drill down(I mean status should be available along X-axis).

I'm expecting visual as below.

Status grouping as below.

Closed ={Closed,Rejected,withdrawn}

OPened = which are not comes under Closed (as above).

In below visual I have taken Creation Date so, closed tickets showing incorrectly.

please help me here.

---

@avanderschilden wrote:

Hello,

Did you use USERELATIONSHIP in the following way;

Measure =
CALCULATE (
    COUNTROWS ( Tickets_Details ),
    USERELATIONSHIP ( 'Calendar'[Date], Tickets_Details[LastUpdated_Date] )
)

It should work...

---

Expecting visual like this

Graph1: