Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

@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

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!

Thanks for reply, I'll check tomorrow.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors