cancel
Showing results for
Did you mean:
Frequent Visitor

Rolling count last 13 month by processing time

Hello,
we have a system with error tickets whose processing time we monitor. The tickets have a start date and an end date. We need to calculate on a rolling basis for the last thirteen months how many tickets have met the runtime target (<=45 days), how many have met the maximum runtime (>45, >=180 days) and how many have exceeded the maximum runtime (>180 days).
I tried a few things in Power BI, but couldn't get it to work.

I have implemented this in Excel for illustration purposes and deposited it at the link below.

4 REPLIES 4
Super User IV

@hmo , You have create a measure like this with a independent date table

Meausre =
var _max = if( isblank(max(Table[closed_on])) || max(Table[closed_on]) >Max(Date[Date]) , Max(Date[Date]), max(Table[closed_on]))
var _min = if( min(Table[date_of_creation]) <Min(Date[Date]) , Min(Date[Date]), max(Table[date_of_creation]))
return
datediff(_min,_max, days)

Very similar to this but do not join - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

The next step is to create an independent range table and using group by ticket_number, create a new measure to use this new table. Refer to this video - https://youtu.be/CuczXPj0N-k

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Frequent Visitor

I'm sorry, I don't understand. I'm not good enough at Power BI.

I do not understand what the above Measure brings for a result and what I should do with it.

Frequent Visitor

Can anyone else help here?

Frequent Visitor

Thanks for reply, I'll check tomorrow.

Announcements

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors