Customer Service Dashboard: Created Tickets, Closed Tickets, Open Tickets combined with Slicer

[Anonymous]

Dear all, 

I have an issue creating a customer service dashboard and also watching multiple youtube tutorials didnt help me: 

I have an excel sheet offering 3 columns: Ticket created (date), Ticket closed (date), Ticket status (closed, pending, in work, blank)

I created a day slicer which lets me select any kind of date range. Now my goal is to create 3 KPIs for the respective date ranges I select in the Slicer:  
1. # of tickets created
2. # of tickets closed

3. # of open tickets 

I already created a separate date table. Thereby, I managed to create the KPI '# of tickets created' but the other two are not possible. I guess especially the third KPI requries some calculation? 

e.g.:
@Greg_Deckler : I saw that you are quite familiar with the topic. Would you be so nice and could help me pleease? Would be such a help! 

Thank you very much and best regards!
Nik

[v-danhe-msft]

Hi @Anonymous ,

Could you please post some simple sample data and your desired result to have a test if possible?

You could see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Daniel He

[Anonymous]

Here is the sample Data: 

https://drive.google.com/file/d/1RER2T34ybjpRX6jrTGuxOf0_Ifzwq0K2/view?usp=sharing

When selecting 01.10.2018-31.10.2018, there should be 393 created tickets (works) and 241 closed ones. I have a problem creating the 'closed tickets' KPI and the 'open ticket' one. 

Thank you very much for your help!! 

Best, 
Nik

[Anonymous]

Hi,

You need to create 2 different tables to get this to work. Because the relationship with date can only affect either Closed or Opened.

To create the new tables you can use the following code when creating a new table

Opened = 
    SELECTCOLUMNS (
        Sheet1,
        "Ticket Number", Sheet1[Ticket number],
        "Opened", Sheet1[Opened]
        )

and

Closed = 
    SELECTCOLUMNS (
        Sheet1,
        "Ticket Number", Sheet1[Ticket number],
        "Closed", Sheet1[Closed ]
        )

Then create the relationship as follows:

Also, dont forget to chance the tiles to the correct opened and closed. As seen below.And here you can find the example i made.

https://1drv.ms/u/s!AgnVgd15_ye0hBfLUybHR65dPEvE

Goodluck!

[Anonymous]

Awesome, thank you very much!! It worked. 

Do you also have a solution for the issue of creating the 'Open Tickets' KPI? I do not know how to create it, as its a bit more complex than the 'created' and 'solved' ones. 

Best, 
Nik

[Anonymous]

With Open Tickets i think you mean the one on the status in work? 

As i do not have sample data with the 4th column i cannot create a preview for you, but if you use the Opened card and add a visual filter on the column status you can filter on that.

Note; for this to work you need to change this code:

Opened = 
    SELECTCOLUMNS (
        Sheet1,
        "Ticket Number", Sheet1[Ticket number],
        "Opened", Sheet1[Opened]
        )

and add the 4th column "status"

Opened = 
    SELECTCOLUMNS (
        Sheet1,
        "Ticket Number", Sheet1[Ticket number],
        "Opened", Sheet1[Opened],
        "Status", Sheet1[Status]
        )

Goodluck!

[Anonymous]

Hi @Anonymous , thank you! 

But I do not have another status column. Rather, I would like to count the tickets, which are created but not closed yet during the respective time range selected in the slicer. I guess it would have to be somehow dynamic? Could you help me with that? Highly appreciated! Thank you! 

Best, 
Niklas 

[Anonymous · Accepted solution]

To create this simply make a measure calculating the difference between Opened and Closed. 

Use the modeling tab to create a new measure. I used the following code to create the proper outcome.

Measure = COUNT(Opened[Opened]) - COUNT(Closed[Closed])

The outcome is like this:

I hope your dashboard can be finished now! 🙂

[Anonymous]

Ah of course! Perfect, thank you very much for your time! 🙂