cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hmo
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.

https://drive.google.com/drive/folders/1UyDjtjScJxHRZLh_8eevhxrycSXqtBZA?usp=sharing 

4 REPLIES 4
amitchandak
Super User IV
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!

hmo
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.

hmo
Frequent Visitor

Can anyone else help here?

 

Thanks in advance!

hmo
Frequent Visitor

Thanks for reply, I'll check tomorrow.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

Get Ready for Power BI Dev Camp

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