HELP: Count values from date table

[jtstacruz]

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!

[Anonymous]

Go to query editor and try pivioting. I can't give you an exact explaination. What I usally do is highlight the two date columns and then pivot them. You will then see that new columns are then added like below

Type             ID      Description      Assignees       Pivot Column                     Date

Request         1                                                       Created Date                01/01/2018

Incident         1                                                       Last Update Time         03/01/2018

Presuming Last Update time means closed, Rename Created date to Open and Last update time to Closed.

Go to create a new column and merge Type & Pivot Column together with a space delimiter. 

When you're happy with that, load the changes.

Use the new column as legend and then if all the months are in one year, use the date heirarchey to add the month only to the axis. For values add the new column to the value section and change to count to see the amount of times a case was opened or closed in a month.

I hope that helps

All the best