I have a Created and Resolved date of tickets, and would like to have one line graph that shows how many tickets were created and resolved each month. Some of the tickets have created months, but have not been completed, so they do not have a resolved date. Right now I can only get a count of created in that month (blue line) and the count of tickets that were created that month, that are now closed (Black line). They way it has it now it does not matter what month they were closed, as it will just be marked as resolved the in the month it was opened
I would like to have it the total count of Resolved in that particular month. As in there may be tickets that were created in July, but were Resolved in August, so I would like to have the resolved count show up in August, not July. This would mean that there are some months more tickets are resolved than created. I imagine it looking liket red and green graph attached.
Solved! Go to Solution.
Here is the high level how it will work:
- You need to have calendar table in your model if it is not there
- set relationship between your create date and resolve date with calendar table on date, you will see one relation is inactive.
- add two measures in your model, one for created tickets and one for resolved tickets, assuming resolved ticket has inactive relationship with calendar table, in this measure calculation use "USERELATIONSHIP" function
- drop line chart with date from calendar table on x axis and use two measure as values.
That is the data I have. The created months and the resolved months. I have a Calander table created. I have created months and resolved months in the same "Combined" query.