Calculating % Close Rate For Service Tickets Over Time Period

[FlyKick]

Hi,

First and foremost, thanks to all the experts that frequent this forum and give up their time to help out. As a Power BI newbie I can't tell you how many times I have found the answer to a problem on here! Epic community! Unforunately my latest issue has got me stumped so first time post for some guidance please.... 

I have a single table imported from a CSV file that contains the following 

• Ticket #
• Client Name
• Entered Date
• Closed Date
• Status
• Summary

Using Cards and Slicers I can display a count of the total open and closed tickets over a specific time period. To achieve this I add two Slicers synced together one for Entered Date and the other for Closed Date. I then use a Card for the Open Tickets and block the Closed Date slicer. Likewise I use a Card for Closed Tickets and block the Entered Date slicer. 

Now I want to display a Card showing the Close Rate % which is ultimately Closed Tickets / Open Tickets. I know I need to use a measure for this. 

I created this measure which accurately displays the total number of closed tickets. Effectively it counts all the rows in the Closed Date column that contain a date. Ultimately if a closed date is present the ticket is closed. 

Total Closed Tickets = counta('TicketHistory'[Closed Date])

I created this measure which accurately displays the total number of open tickets. Effectively it counts all the rows in the Closed Date column that are blank. 

Total Open Tickets = CALCULATE( COUNTROWS('TicketHistory'), 'TicketHistory'[Closed Date] = BLANK())

Logically I thought if I mashed these together into a third measure and then used a slicer to filter the date it would work. Thus I produced the following measure. 

Ticket Close Rate = COUNTA('TicketHistory'[Closed Date]) / CALCULATE( COUNTROWS('TicketHistory'), 'TicketHistory'[Closed Date] = BLANK())

Not only does the above measure not seem to return the correct result but it also won't work because the result is going to be displayed on a single card and if I add both slicers to a single card it uses the AND logic which excludes tickets that should be included. 

I am so lost on this one I don't even know what to search for to point me in the right direction and hoping someone in the community has come across this in the past and can give me a few pointers....

Cheers,

Fly Kick

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[az38]

Hi @FlyKick 

I didnt check but maybe it would be better to use next to avoid any invisibe filters

Total Closed Tickets = calculate(counta('TicketHistory'[Closed Date]), ALL('TicketHistory'))

[FlyKick]

AZ38 thanks for the tip but I that didn't work 😞 The challenge here is 

I want to calculate the Close Rate % =  Total Closed Tickets  / Total OpenedTickets 

I want to use a time period of last week (ideally it would be great if I can adjust to any time period using a slicer) but not essential.

A Closed ticket is any ticket with a Closed Date in the time period.

An Opened ticket is any ticket with an Entered Date in the time period.

I can use two slicers one for Closed Date and one for Open Date on the visual but as the logic is AND it will only show a ticket that was Opened AND Closed in the time period. Therefore the Close Rate % is always 100%. 

I know I need to use a measure to achieve this but I don't know if it is possible to 

a) use a time period within a measure? 

b) if a is possible use a variable or provider some interactive way for the user to adjust the time period variable

This measure accurately counts the total closed tickets

Total Closed Tickets = counta('Current-Ticket History'[Closed On])

This measure only counts all the tickets that are still open. I.e ones that have no close date, it does not count tickets that were entered and closed in the time period.

Total Open Tickets = CALCULATE( COUNTROWS('Current-Ticket History'), 'Current-Ticket History'[Closed On] = BLANK())

If I was to write it out as pseudo code it would look something like

1. TimePeriod = User Defined Date Period (i.e. last week or last month or custom period)
2. ClosedTicketsVar = Count of all tickets with a closed date in $TimePeriod
3. OpenTicketsVar = Count of all tickets with an entered date in $TimePeriod
4. Ticket Close Rate = $ClosedTicketsVar / $OpenTicketsVar