Forum Discussion

sullynivek's avatar
sullynivek
Frequent Visitor
8 years ago

Calculating 'mean time between failures'

Hi All,

 

I am stuck on this problem, I need to calculate the MTBF of my companies assets.

 

There are 2 dates in the table 'org_date' (origination date - this is when the job is created) and 'cmpl_date' (this is when the job was completed). What i require is for the measure to give me the result of what is the date difference between the last completed job and the newly created job on any unique asset.

 

Columns are: WorkOrder | Asset Desc | Work Req | org_date | cmpl_date | MTBF

 

Where MTBF column above has the following measure:

 

Mean Time Between Failures = 
AVERAGEX (
    wkaw,
    VAR CurrentOrgDate = wkaw[org_date]
    VAR CurrentNo = wkaw[Index]
    VAR PreviousCompDate =
        CALCULATE (
            MAX ( wkaw[cmpl_date] ),
            ALLEXCEPT ( wkaw, wkaw[assetshortdesc] ),
            wkaw[cmpl_date] <= CurrentOrgDate,
            wkaw[Index] < CurrentNo
        )
    RETURN
        IF (
            NOT ( ISBLANK ( PreviousCompDate ) ),
            1
                * ( CurrentOrgDate - PreviousCompDate )
        )
)

Unfortunately this isnt giving me accurate results

 

Any help would be appreciated.

 

kevin

Need Help

5 Replies

  • mattbrice's avatar
    mattbrice
    Solution Sage
    8 years ago

    Can you try this?

     

    Mean Time Between Failures =
AVERAGEX (
    wkaw,
    VAR CurrentOrgDate = wkaw[org_date]
    VAR CurrentNo = wkaw[Index]
    VAR PreviousCompDate =
        CALCULATE (
            MAX ( wkaw[cmpl_date] ),
            FILTER (
                ALLEXCEPT ( wkaw, wkaw[assetshortdesc] ),
                wkaw[cmpl_date] <= CurrentOrgDate
                    && wkaw[Index] < CurrentNo
            )
        )
    RETURN
        IF (
            NOT ( ISBLANK ( PreviousCompDate ) ),
            1
                * ( CurrentOrgDate - PreviousCompDate )
        )
)
    • sullynivek's avatar
      sullynivek
      Frequent Visitor
      8 years ago

       

      There are many assets amongst this dataset, what i have done is to filter to just 1 asset type. The required result would be to have the completed date of the last known failure and the origination date of the newest failure to be a measured time laspe by days i.e. last known failure completed date 1/12/17 , newest failure origination date is 12/12/17 - days between failure would be 11 days as i wouldn't want to include the origination failure date. 

       

      This where i found the original measure - https://community.powerbi.com/t5/Desktop/repair-metrics-MTTR-MTBF/td-p/191653

       

      Hope this makes more sense.

       

      Thanks

       

      kevin.

  • sullynivek's avatar
    sullynivek
    Frequent Visitor
    8 years ago

    Hello,

     

    Just an update to the above, I have now made some progress. I now get the correct result i.e. the mean time in days between breakdowns. But the table is not populated with each increment of days in between each record. The measure is as follows:

     

    Mean Time Between Failures = 
AVERAGEX (
    wkaw,
    VAR CurrentOrgDate = wkaw[org_date]
    VAR CurrentNo = wkaw[Index]
    VAR PreviousCompDate =
        CALCULATE (
            MAX ( wkaw[cmpl_date] ),
            
                ALLEXCEPT( wkaw, wkaw[assetno] ), FILTER (wkaw,
                CurrentOrgDate >= wkaw[cmpl_date] ), wkaw[Index] <> CurrentNo 
                   
            
        )
    RETURN      
       
        IF (
            NOT ( ISBLANK ( PreviousCompDate ) ),
            1
                * ( CurrentOrgDate - PreviousCompDate )
        )
)

    below you can see it provides the total MTBF: