Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Calculating Mean Time Between Failure

@Amit, @Greg , @tamerj1 , @lbendlin 

 

@amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler 

 

@christinepayton @audreygerred 

 

LukeB,

 

Hello PowerBI expert folks,

 

My 1st column has a list of Equipment, and the 2nd column has the date/time they failed in the date/time format. For each of these equipment, I am trying to find the difference between date/time they failed (difference between rows) and calculate their summation at the end.

 

I created the Index column and wrote the following DAX code to solve this issue:

 

DIFF =

var _lasttime = CALCULATE(MAX('Sampling'[BreakDownDate]),FILTER('Sampling','Sampling'[Index]=EARLIER('Sampling'[Index])-1))

return

DATEDIFF(_lasttime,'Sampling'[BreakDownDate],MINUTE)

 

It is giving me the right number if I select(filter) just 1 equipment but as I keep adding Equipments to the table it gives me wrong summation of Breakdown Date towards the end. As a whole on 2000+ different equipments, my numbers are not that far. But individually for those equipments my numbers are sharply and significantly different for many equipments. 

 

The other DAX code I wrote was:

 

Difference 5 =
var _lasttime = CALCULATE(MAX('Sampling'[BreakDownDate]),FILTER('Sampling','Sampling'[BreakDownDate]<EARLIER('Sampling'[BreakDownDate])))
return
DATEDIFF(_lasttime,'Sampling'[BreakDownDate],MINUTE)
 
The code above is giving me the right number on an individual equipment(filtering) basis but as a whole(summation of the list of equipments towards the end) it is giving me slightly off number.

 

Can you please suggest a better DAX code than above to resolve this issue further?

 

I will more than appreciate any of your help/suggestions.

 

Thanks

 

Need Help

14 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    I assume you have varying number of failure entries per equipment?  You can use OFFSET to calculate the individual gaps per equipment and then average it for each equipment. Then what?  Average that average across equipments?  Any weighting?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello lbendlin ,

     

    This is way too complicated. I don't even use the M-Query or the Advanced Editor. Can you suggest a DAX formula for this?

     

    Thanks,

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      MTBF DAX = 
var b = MAXX(OFFSET(-1,ALL('Table'),PARTITIONBY([Equipment]),MATCHBY([BreakDownDate])),[BreakDownDate])
Return if(not ISBLANK(b),[BreakDownDate]-b)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hello lbendlin 

         

        The code above is not working--It is giving error like below:

         

         

        The error is mainly in regards to using PARTITIONBY and MATCHBY Commands. Do I need to write 'Table'[Equipment] since Table is a data table and Equipment is a column variable inside of that data table? Can you please suggest a better working code. 

         

        Thanks,

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hello

    @Amit, @Greg , @tamerj1 , @lbendlin 

     

    @amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler 

     

    @christinepayton @audreygerred 

     

    @LukeB,

     

    I have a data table called pmAsset where it has a List of Equipment that has never failed.

    I have another data table Table2 where it has a List of Equipment that has failed with a specific Breakdown Date.

    I have adopted the following methodology to calculate Mean Time Between Failures:

     

    I have merged the pmAsset and Table2 dataset based on common variable- ‘Equipment’ in Table2 and variable ‘Code’ in pmAsset based on Inner Join(only matching rows).

    pmAsset data table has a variable called StartDate that shows the dates when Assets were commissioned.

     

    After that, I wrote the following Calculated Column to calculate Mean Time Between Failure:

     

    TimeBetweenFailures = DATEDIFF(MergedTable[pmAsset.StartDate],MergedTable[BreakDownDate],DAY)

     

    MTBF = AVERAGE(MergedTable[TimeBetweenFailures])

     

     

    After that, I put BreakdownDate on the X-axis and MTBF on the Y-axis on a StackedColumnChart but this gave me very unconvincing results for MTBF where I got a constant value of 4700 days for all the months. So, I know this is incorrect.

    I then adopted another methodology to calculate Mean Time Between Failure:

     

    Operating Time = DATEDIFF('MergedTable'[StartDate].[Date],'MergedTable'[BreakDownDate].[Date],DAY)

     

    TotalOperatingTime = SUM(MergedTable[Operating Time])

     

    Number of Failures = COUNT(MergedTable[BreakDownDate])

     

    Mean Time Between Failures = DIVIDE('MergedTable'[TotalOperatingTime],'MergedTable'[Number of Failures])

     

    After that, I put BreakdownDate on the X-axis and MTBF on the Y-axis on a StackedColumnChart but this gave me very unconvincing results for MTBF where I got weird set of values like either null or -31, -29 etc..

     

     

     

     

    Can you please suggest the right methodology to calculate the Mean Time Between Failures?

     

    pmAsset data table and Table2 data table has many other variables like BreakDownDate, DueDate, CompletionDate, StartDate, RepairStartDate, RepairCompletedDate etc..

     

    Also, can you please suggest-what values to use in the X-axis—BreakDownDate or DueDate or CompletionDate or StartDate?

     

    Also, do I need to take other variables into consideration like RepairStartDate or RepairCompletedDate to calculate the Mean Time Between Failures?