Forum Discussion
Adding Up Tickets Per Day
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
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
7 Replies
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
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
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])))ThanksMickHi 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
