Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sreeni
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
avanderschilden
Resolver I
Resolver I

Hello,

 

Did you use USERELATIONSHIP in the following way;

 

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

It should work...

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 thisExpecting visual like this

 

Graph1:

Chart.gif

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors