How to calculate MTBF, MTTR , MTTF , MTTA in query editor based on data from analytic view of devops

[Jeyeline]

Hello Team,

  We are using analytic view of Microsoft Azure Devops to integrate it with Power BI. The normal incident columns that we had in our devops are mentioned in the attached images

Given these fields, we are uncertain about how to automatically calculate metrics like MTBF, MTTR, MTTF, and MTTA using DAX queries. Is there any ways to get it done?

[Greg_Deckler]

@Jeyeline See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[Jeyeline]

Hello @Greg_Deckler ,

Thanks for your reply. I have gone through it already and tried with some DAX. However, I am facing an error over these. It would be grateful if I get guidance in creating a separate DAX for it.

MTBF - If we are required to calculate MTBF for a particular month, we need to have DAX which is supposed to calculate the MTBF based on (the sum of values of a particular work item' lead time (Incident)days- days in the month) divided by its no of occurrences (WIT-Incident) in that month.

MTTR - If we are required to calculate MTTR for a particular month, we need to have DAX which is supposed to calculate the MTTR based on the sum of values of a particular work item' lead time (Incident) divided by its no of occurrences (WIT-Incident) in that month.

Would you please help us in creating DAX queries?

[Greg_Deckler]

@Jeyeline Can you post sample data or a PBIX file? 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Jeyeline]

Hi @Greg_Deckler,

Please find the information and the attached data.

MTBF - The Lead time of all incidents (in hours) is subtracted from the total number of hours of a month, and the value is divided by the number of incident occurrences.
(where lead time is the date diff between the created date and the closed date for each work item and convert to hours)

MTTR - Sum of lead times(in hours) for the month divided by the no of occurrences in that month.

[Jeyeline]

ID	Work Item Type	Title	Created Date	Closed Date
29758	Incident	Incident 1	11/29/2023 12:52:51 PM	01/02/24 10:07
29768	Incident	Incident 2	11/29/2023 1:05:56 PM	11/29/2023 1:06:00 PM
30348	Incident	Incident 3	12/04/23 17:07
30355	Incident	Incident 4	12/05/23 12:33	12/29/2023 10:09:07 AM
30360	Incident	Incident 5	12/05/23 13:04	12/29/2023 10:10:06 AM
30814	Incident	Incident 6	12/08/23 11:58	12/29/2023 10:10:55 AM
30819	Incident	Incident 7	12/08/23 12:09	12/29/2023 10:11:35 AM
30834	Incident	Incident 8	12/08/23 12:22	12/29/2023 10:14:26 AM
30851	Incident	Incident 9	12/08/23 12:51	12/29/2023 10:15:45 AM
30854	Incident	Incident 10	12/08/23 12:56	12/29/2023 10:17:32 AM

[Greg_Deckler]

@Jeyeline OK, so I believe I see the potential issue here. You want reporting on a monthly grain but you have incidents that span months. You'll need to do something along the lines of these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Is that the issue you are running into?

[Jeyeline]

Hello @Greg_Deckler ,

 The second link partially helps. We are having records with Bugs, Incident, CRs for overall duration. On that, we need to filter based on the month for the incident work item type.


Then, we need to calculate the lead time (like the second chart based on the created date and closed date) divided by the number of occurrences for MTTR

[Greg_Deckler]

@Jeyeline It's an interesting and complex issue, let me see what I can do. Going to take a crack at it.

[Greg_Deckler]

@Jeyeline OK, see if this is what you are going for. PBIX is attached. This is based on your sample data. This is for MTTR, MTBF should follow naturally from the same kind of logic.

MTTR = 
    VAR __Incidents = ADDCOLUMNS('Table',"Effective Date",IF(ISBLANK([Closed Date]),TODAY(),[Closed Date]))
    VAR __Table =  
    SELECTCOLUMNS(
        FILTER(
            GENERATE(
                __Incidents,
                'Dates'
            ),
            [Date] >= DATEVALUE([Created Date]) &&
            [Date] <= DATEVALUE([Effective Date])
        ),
        "ID",[ID],
        "Date",[Date]
    )
    VAR __Table1 = GROUPBY(__Table,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))
    VAR __NumIncidents = COUNTROWS(__Table1) // Number of incidents in month
    VAR __IncidentsInMonth = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                FILTER( __Incidents, [ID] IN SELECTCOLUMNS(__Table1, "ID", [ID])),
                "NewEffectiveDate", 
                        VAR __EOMonth = EOMONTH(MAX('Dates'[Date]),0) + TIME(23, 59, 59)
                        VAR __Result = IF([Closed Date] > EOMONTH(MAX('Dates'[Date]), 0), EOMONTH(MAX('Dates'[Date]), 0), [Effective Date])
                    RETURN 
                        __Result,
                "NewCreationDate", 
                        VAR __BOMonth = MIN('Dates'[Date])
                        VAR __Result = IF([Created Date] < __BOMonth, __BOMonth, [Created Date])
                    RETURN
                        __Result
            ),
            "Diff", [NewEffectiveDate] - [NewCreationDate]
        )
    VAR __TotalTime = SUMX(__IncidentsInMonth, [Diff])
    VAR __Result = DIVIDE( __TotalTime, __NumIncidents )
RETURN
    __Result

[Sukh-ErdeneB]

Can you support MTTFpm ?

[Jeyeline]

@Greg_Deckler , Thanks for the response. Will check it out.

[Jeyeline]

@Greg_Deckler , would like to know the purpose of the fields - effective date, new effective date and new creation date.

[Greg_Deckler]

@Jeyeline So basically this is accounting for when an incident spans months and is not closed yet. If I remember the code, if the incident is not closed, I fill it in with today's date and time so that calculations for the current month account for the fact that this incident is at least ongoing at the present time. The new effective date and new creation date are accounting for spanning months. So let's say an incident opens in November and closes in January. For calculating the monthly MTTR for November, the end date needs to be November 30th. For December, the creation date is December 1st and end is December 31st. For January the creation date is January 1st and the end date is the day the ticket was closed. Hopefully that makes sense, but the logic is that if you have issues that span months but you want a granularity of month for your calculations, you need to essentially "invent" the monthly grain data points within your measure.

[Jeyeline]

I was wondering if it might be possible to achieve the desired outcome without introducing additional fields for new effective data. If we calculate the normal span between months, wouldn't it automatically determine the difference between the created and closed dates?

I just wanted to confirm this with you.

[Greg_Deckler]

@Jeyeline Not entirely certain what you are getting at. Went down this path because you wanted to get the MTTR or MTBF for a particular month. So, that said, let's say you have an incident that starts in January and ends in March. What then is the MTTR or MTBF for that incident in February and how do you get that incident into context in the first place for February? Or in those cases do you want to ignore it completely or ?

Sure, at a macro level (ignoring month granularity) you could calculate these values based upon the original creation and closed dates. However, at that point you would then have to compute an average I suppose across the number of months that are in context. So if you computed MTTR or MTBF for the overall data set, you would then need to count how many months are in context (let's say 3) and then divide your value by 3 to give you the monthly MTTR/MTBF for example.