I have a graph which shows the count of [ticket No] versus Month(closed date). Filtering the [status] to only "close", it displays the number of closed tickets versus month by which they were closed.
Now, I want to convert this count into percentage to calculate the KPI of each month, and it gets a little bit tricky.
The way it works is, to calculate the KPI, I need to pull the number of closed ticket of that particular month over number of open tickets up to that month( that month and prior).
to calculate the KPI of April, it would be like this ; No. of closed ticket in April x 100
No. of open ticket up to April(Jan, Feb, Mar)
My first idea was to use a calculated field to return the count of ticket which satisfy the condition where Month(closed date) = 4 and [status]="closed'. But I realized I also need to calculate the KPI for subsequent months. So, using Month(closed date) = 4 is not quite feasible as the month of the closed date is fixed.
therefore, the formula for KPI that can be used for all months would be:
No. of closed ticket in a respective month x 100
No. of open ticket up to that respective month
thus, how do I create a calculated field that return count of ticket no with the condition of status = closed and only for that particular month.
I have another idea to use CASE for KPI calculation of every month. But I'm wondering if there is a better way to do it.