topic Re: Rolling count last 13 month by processing time in DAX Commands and Tips

Rolling count last 13 month by processing time

hmo — Tue, 30 Mar 2021 10:32:59 GMT

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

Re: Rolling count last 13 month by processing time

amitchandak — Tue, 30 Mar 2021 15:11:29 GMT

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)

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

Re: Rolling count last 13 month by processing time

hmo — Tue, 30 Mar 2021 15:24:12 GMT

Thanks for reply, I'll check tomorrow.

Re: Rolling count last 13 month by processing time

hmo — Thu, 01 Apr 2021 11:30:23 GMT

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.

Re: Rolling count last 13 month by processing time

hmo — Mon, 05 Apr 2021 10:06:23 GMT

Can anyone else help here?

Thanks in advance!