Need help writing a measure to find count of open tickets

[Prath]

Need help writing a measure to find the count of open tickets as a line in the attached report which can be filtered by "Team" and be able to drill down to a week or month hierarchy.

PBIX file uploaded to drive: https://drive.google.com/file/d/1wR7V-JjLN3jmLWEJ8ESffn9yAT8VQ-aF/view?usp=sharing

Chart

[AlexAlberga727]

@Prath 

I had to make a couple adjustments to your model. My output is the below - 

With this you're able to quickly review which tickets below to what team, as well as see which tickets are still Open.

Some things I had to adjust - 

1. Marked [Calendar_Date] as DateTable.

2. Changed [Ticket_Num] to a Text Format.

3. Adjusted Relationship between DateTable and [Ticket_List]

4. Created Calculated Column to apply a "Status": Open/Closed

5. Created Measure to calculate [Total Tickets] = DISTINCTCOUNT( Ticket_list[Ticket_Num] )

6. Created Measure to calculate [Total Open Tickets] = CALCULATE( [Total Tickets], Ticket_list[Status] = "Open" )

7. Created Measure to calculate [Total Closed Tickets] = CALCULATE( [Total Tickets], Ticket_list[Status] = "Closed" )

8. Created Table within Report to show records

9. Created Slicers within report to Slice by Team and Status

10. Created Card to display Values

11. Created Bar Chart to show Closed vs Open

Let me know if this is the route you're looking to take - If needed connect with me and I can walk you through the steps I took.

Also - Please mark as Solution and give me a thumbs up if you would like!

Enjoy!

[amitchandak · Accepted solution]

Very similar to what I discussed in : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Can you see if that can help

Formula like this

Open ticket = CALCULATE(COUNTX(FILTER(Ticket_list,Ticket_list[Created_Date]<=max(Calender_Date[Date]) && (ISBLANK(Ticket_list[Closed_Date]) || Ticket_list[Closed_Date]>MAX(Calender_Date[Date]))),Ticket_list[Ticket_Num]),CROSSFILTER(Ticket_list[Created_Date],Calender_Date[Date],None))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Prath]

@amitchandak Appreciate your help. And the blog post is very helpful in my context. Your measure formula is exactly what I need.

One issue:

When I add date filter, "Open Ticket" count varies accordingly(as shown in photos). Is there a way to get the actual open ticket count from table data rather than filtered data?  In other words, is there a way for this measure to ignore this specific filter?

Can count still be 5, 10, 10 ?