Adding Up Tickets Per Day

[micklowe]

Hi All,

I have a CSV datasource of support tickets, I want to create measures to add up opened and closed tickets per day but struggling a bit. I have tried just using counts on the field but all i get is a total of all tickets in the data.

I have created a date table then tried adding various measures in there but same result, I tried linking the "date created" field to the date table but that mad it worse.

Anyone know how I can do this please?

Thanks

Mick

[v-jayw-msft]

Hi @micklowe ,

You will need to create two measures.

open = CALCULATE(COUNT('Table'[ticket]),FILTER(ALL('Table'),'Table'[open]<=SELECTEDVALUE('calendar'[date])&&'Table'[close]>SELECTEDVALUE('calendar'[date])))

close = CALCULATE(COUNT('Table'[ticket]),FILTER(ALL('Table'),'Table'[close]<=SELECTEDVALUE('calendar'[date])))

If I misunderstand your requirement, please show some sample data and expected result.

Best Regards,

Jay

[micklowe]

Hi Jay,

I have moved on to a live data feed now and there is a slight change in the way i think it needs to work. I have a created date which should give me tickets opened on a specific day but the closed one is a bit more complicated in that I need to check the ticket status = 5 along with the updated date field.

These are the measures currently but the numbers aren't coming out correctly:

open = CALCULATE(COUNT('Tickets'[id]),FILTER(ALL('Tickets'),'Tickets'[created_at]<=SELECTEDVALUE('Dates'[Date])&&'Tickets'[updated_at]>SELECTEDVALUE('Dates'[date])))

close = CALCULATE(COUNT('Tickets'[id]),FILTER(ALL('Tickets'),'Tickets'[updated_at]<=SELECTEDVALUE('Dates'[date])))

Thanks

Mick

[v-jayw-msft]

Hi @micklowe ,

Better to share some sample data and describe your logic. For example, you said "give me tickets opened on a specific day". Does it mean you only want to calculate the count of ticket which open date = a specific day? If I have two tickets, ticket1 with open date 10/1/2021 and close date 10/3/2021, ticket2 with open date 10/2/2021 and colse date 10/3/2021. Then the count of open ticket for 10/2/2021 is 1? The same with close tickets and need to check if status = 5?

Best Regards,

Jay

[micklowe]

Hi Jay,

Not sure how to get the data out as its coming from an API.

I have changed the measures today and the open one now looks correct:

open = CALCULATE(COUNT('Tickets'[id]),FILTER(ALL('Tickets'),'Tickets'[created_at].[Date]=SELECTEDVALUE('Dates'[Date])&&'Tickets'[updated_at]>SELECTEDVALUE('Dates'[date])))

The closed one needs to filter on two variables, the updated_at column and another column called "status" which I only need to add up where this equals "5":

close = CALCULATE(COUNT('Tickets'[id]),FILTER(ALL('Tickets'),'Tickets'[updated_at].[Date]=SELECTEDVALUE('Dates'[date])))

Thanks

Mick

[v-jayw-msft · Accepted solution]

Hi @micklowe ,

If the [status] is a column in the 'Ticket' table, you could add the [status] to the filter condition.

For example:

close = CALCULATE(COUNT('Tickets'[id]),FILTER(ALL('Tickets'),'Tickets'[updated_at].[Date]=SELECTEDVALUE('Dates'[date])&&'Tickets'[status] = 5))

If it's something else, please share more information.

Best Regards,

Jay

[micklowe]

Thanks Jay, this almost gives me what I want, the numbers aren't an exact match to what I was expecting but thats down to the data not the query 🙂

[amitchandak]

@micklowe ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

But this blog should help, on the similar topic

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...