Calculate the number of new, open, closed tickets per day or week

[Anonymous]

Hi,

I'm a relatively newbee regarding PowerBI and working on some reports linked to a ticketing tool, TOPdesk.

I would like to calculate and report the number of new, open and closed tickets over a period of time, or on a selected date.

Tried several measures, but I'm not getting there. Hope someone can give me some direction. 

Here's some example data. 

TicketID	creationDate	completed	completionDate
123	1-2-2020	True	1-3-2020
234	15-2-2020	False
345	3-3-2020	True	17-3-2020
456	17-3-2020	False
567	4-5-2020	True	5-5-2020
678	14-5-2020	True	16-5-2020
789	17-9-2020	True	19-9-2020

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I think you can create a calendar table and three measures in card.

Like this:

new = var a=SELECTEDVALUE('Table 2'[Date])
return COUNTX(FILTER(ALL('Table'),[creationDate] =a),[TicketID])+0

open = var a=SELECTEDVALUE('Table 2'[Date])
return COUNTX(FILTER(ALL('Table'),[creationDate] <a&&([completionDate]>a||[completionDate]=BLANK())),[TicketID])+0

closed = var a=SELECTEDVALUE('Table 2'[Date])
return COUNTX(FILTER(ALL('Table'),[completionDate]<=a&&[completionDate]<>BLANK()),[TicketID])+0

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @amitchandak . I'll dive in to it. 

[amitchandak]

@Anonymous , Seem like very similar to my HR blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970