Tickets Opened and Closed per Month

[MeghnaZutshi]

I have a data set for last 24 months. Need the following in a line chart:

1. Total tickets opened in a month

2.  Total tickets closed in a month

Please help here. Thank you in advance.

Here is a subset of data:

Number	Priority	Assignment group	Opened	Resolved time
INC100	3 - Medium	abc	2021-01-02 09:40:01	2021-01-04 06:37:25
INC101	3 - Medium	abc	2021-01-02 09:42:55	2021-01-02 09:42:55
INC102	5 - Low	abc	2021-01-03 15:32:17	2021-01-19 10:27:59
INC103	3 - Medium	abc	2021-01-04 08:27:09	2021-01-11 13:10:22
INC104	4 - Minor	abc	2021-01-04 10:59:40	2021-02-26 02:41:54
INC105	2 - High	abc	2021-01-04 12:50:00	2021-01-04 17:21:56
INC106	3 - Medium	abc	2021-01-04 13:00:30	2021-01-04 14:24:04
INC107	3 - Medium	abc	2021-01-04 15:12:45	2021-01-05 22:48:21
INC108	2 - High	abc	2021-01-04 15:33:30	2021-01-04 22:41:45
INC109	5 - Low	abc	2021-01-04 16:31:23	2021-02-24 19:28:30

[Anonymous]

Hi @MeghnaZutshi ,

According to your description, here are my steps you can follow as a solution.

(1)  My test data is the same as yours.

(2) We can create a table.

Date = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))

(3)We can create two measures.

count open = 
 var _a=COUNTROWS(FILTER(ALL('Table'),MONTH('Table'[Opened])=MONTH(MAX('Date'[Date]) )))
 return IF(_a=BLANK(),0,_a)

count resolved = 
 var _a=COUNTROWS(FILTER(ALL('Table'),MONTH('Table'[Resolved time])=MONTH(MAX('Date'[Date]))))
  return IF(_a=BLANK(),0,_a)

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[chris_drabek]

These are the steps I would take:

• Depending on how important having the time for Opened and Resolved Time, I'd either format the fields as Date, or duplicate the fields and create Date fields.
• Bring in a Calendar table and join it to both of the Opened Date and Resolved Date fields (one is inactive).
• Create an Opened measure.  If your active join to the calendar table is to Opened Date, then you would just have it count the Number field.
• Create a Resolved measure.  If your active join to the calendar table is Opened Date, then you'd need to include USERELATIONSHIP in your measure to activate the join to the Resolved Date.
• Use Year and Month from the calendar table, then use your Opened measure and Resolved measure in the line visual.