cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sreeni Frequent Visitor
Frequent Visitor

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

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 NumberCREATION_DATELastUpdated_DateResolution Status
665271/20/2019 0:001/20/2019 0:00DRAFT
665281/20/2019 0:003/20/2019 0:00PENDING APPROVAL
665291/20/2019 0:001/20/2019 0:00PENDING APPROVAL
665301/20/2019 0:001/20/2019 0:00UNDER REVIEW
665311/20/2019 0:001/21/2019 0:00UNDER REVIEW
665321/20/2019 0:003/20/2019 0:00DRAFT
665331/20/2019 0:001/21/2019 0:00IMPLEMENTED
665341/20/2019 0:001/20/2019 0:00UNDER REVIEW
665351/20/2019 0:002/21/2019 0:00UNDER REVIEW
665361/20/2019 0:001/21/2019 0:00UNDER REVIEW
665371/20/2019 0:001/20/2019 0:00DRAFT
665381/20/2019 0:001/20/2019 0:00DRAFT
665391/20/2019 0:001/20/2019 0:00SCREEN
665401/20/2019 0:001/20/2019 0:00UNDER REVIEW
665411/20/2019 0:001/20/2019 0:00UNDER REVIEW
665421/20/2019 0:001/20/2019 0:00DRAFT
665431/21/2019 0:001/21/2019 0:00UNDER REVIEW
665441/21/2019 0:001/21/2019 0:00UNDER REVIEW
665451/21/2019 0:003/28/2019 0:00UNDER REVIEW
665461/21/2019 0:001/21/2019 0:00UNDER REVIEW
665471/21/2019 0:001/21/2019 0:00DRAFT
665481/21/2019 0:002/21/2019 0:00WITHDRAWN
665491/21/2019 0:001/21/2019 0:00DRAFT
665501/21/2019 0:001/21/2019 0:00DRAFT
665511/21/2019 0:001/21/2019 0:00SCREEN
665521/21/2019 0:001/21/2019 0:00UNDER REVIEW

 

Regards

SRRY

 

2 REPLIES 2

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

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 Frequent Visitor
Frequent Visitor

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

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...


123.gifExpecting visual like this

 

Graph1:

Chart.gif

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors