Create Line graph showing created vs solved tickets

[kerblooy]

I know the the title makes it sound like this is an easy problem, and it might be, but I'm at a complete loss.

I have Power BI Desktop with a Zendesk datasource.

I want to create a line graph that shows Created vs Solved tickets over a 7 day period.

I actually have it all made, I have a Calendar table and a filter to show 7 days and I have 2 lines on my graph.

The problem I'm having right now is this:

Tickets fall into 1 of three categories:

1. Ticket created in the 7 day period

2. Ticket solved in the 7 day period

3. Ticket created AND solved in the 7 day period

I'm counting the first two types of tickets with no problem, the issue with the third is that if the ticket was solved, it's not being counted as a ticket that was created.  Obviously causing the created ticket data to be drastically off.

I have my tickets table that includes an entry for every ticket as well as it's created_at_date and status (open, solved, e.t.c.)

I originally had the values in these graphs seperate i.e. one graph showing tickets created and one graphs showing ticket solved, but I want to combine them so I can easily tell if we solved more than were created in a day.

In the Tickets Created graph I counted tickets based on their created_at_date in a 7 day period.

In the Tickets Solved graph I counted tickets based on their status (solved or closed) and their updated_at_date (last updated_at_date would be when it was solved or closed).

Since a line graph can only go by 1 date in the Axis, I created a column called "static_date" with the following forumula:

Static_Date = IF(Tickets[status] = "solved" || Tickets[status] = "closed", Tickets[updated_at_date], Tickets[created_at_date])

I quickly realized that this formula wont count tickets created and solved in the 7 day period as created tickets, just as solved tickets.

I'm sorry for the long explanation, but how in the world can I solve this?

[Anonymous]

I'm wondering if you are using the wrong approach.  I'd be expecting that you want to create a measure to count Tickets created and a separate measure for count of solved tickets.  Then on the graph, both these measures can share the same Date axis.

[kerblooy]

Thanks for the reply!

That is an easier way to approach, sadly the date thing still remains a problem.

One point I forgot to mention in the OP is that sometimes the created date and the solved date are not the same.

So if a ticket was created on 5/1 and solved on 5/2, which date would be plotted on the graph?

Is there a way I can get it to plot both the created and solved date for the same ticket?

[Anonymous]

My thought here is that "Date Created" would be used in the creation metric and "Date Solved" for the solved metric.  The graph would need a common axis, which would be best served by linking your table to a Dimension date table.  This dimension date table is then used as your axis.

Here you might need to consider how your measures are written, as you might need to use some sort of ALL style statements to ensure that the context of the first measure isn't upsetting the context of the 2nd.

Here is some info on Date Dimensions:
http://www.agilebi.com.au/power-bi-date-dimension/

[kerblooy]

Thank you very much for all the info, sounds like what you says works and my lack of knowledge/experience with BI just makes this problem seem a lot more complicated than it is.

I'll do some reading and hopefully, I can get this done.

Thanks again!