HELP: Count values from date table and filter out specific type

[jstacruz]

Hello all! I would appreciate anyone's assistance with the following.

I have a dataset that consists of ticket information such as type (request or incident), created date, and last update time. (See attached sample below).

What I want to happen is a line chart that would look like this:


I want a line chart that would show both: Incidents Created, Incidents Closed, Requests Created, and Requests Closed per month. Meaning those values should contain the number of tickets filtered by type and the corresponding time for each. I already achieved getting Incidents Created and Requests Created because I just had to filter out the type (incident and request) using CALCULATE and COUNTROWS function because by doing so it will automatically detect the relationship to its Created Date therefore displaying the trend of created tickets per month.

However, I'm having a complicated time analyzing to get Incidents Closed and Requests Closed because it must be based on Last Update Time table. I tried using this measure:

E.G:

Incident Closed = COUNTROWS ( FILTER ( Append2, Append2[Type] = "Incident" && Append2[LastUpdateTime] )) +0

When I tried this measure, it doesn't filter out both Last Update Time values and its specific "type" (incident or request). It just counts the values for its type. Is there any other measure or procedure I could apply to get the necessary output that I need? Working on this project right now and any quick replies will be very much appreciated.

Thank you so much!

[jthomson]

Use a calendar table, create relationships for each date field you need to use, and set the calendar table as your axis. I'm not really sure what you're trying to actually do with the date in that formula, just listing a field on its own doesn't make sense

[jstacruz]

I'm just a beginner so I'm kinda challenged on how to execute it. I already have an axis set which is displays month-year. Dragging "Last Update Time" to my values would just be an option however -- it would count both values for incident and requests. What I want to achieve is to filter it by type (from another column) so that I could get seperate values for "Incidents Closed" and "Requests Closed". 

[Ashish_Mathur]

Hi,

Share the link from where i can download your Excel file.