Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Rolling count last 13 month by processing time

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. 

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]))
datediff(_min,_max, days)


Very similar to this but do not join -


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 -

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?


Thanks in advance!

Frequent Visitor

Thanks for reply, I'll check tomorrow.

Helpful resources

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors