Calculating 'mean time between failures'

[sullynivek]

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

[sullynivek]

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:

[Ashish_Mathur]

Hi,

Share a sample dataset and show the expected result.

[sullynivek]

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.

[mattbrice]

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]

@mattbrice - i tried what you suggested, but it gave me the same result.