Calculating Mean Time Between Failure

[shreep1]

@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

[shreep1]

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?

[shreep1]

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]

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

[shreep1]

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,

[lbendlin]

Sounds like you are running a rather old version of Power BI Desktop?

[shreep1]

Hello @lbendlin,

The PowerBI desktop I am using is February 2023 version. Is my PowerBI old enough for this formula to not work?

[lbendlin]

Indeed. The window functions were introduced after that version.

[lbendlin]

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?

[shreep1]

Hello @lbendlin ,

Can you please include your idea in my DAX code-Not sure how to write that inside of my DAX code? 

Thanks

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[shreep1]

Hello @lbendlin ,

I have attached the sample file. Please let me know how you would implement your code in this sample data or if you have any other questions. 

Thanks

Equipment	BreakDownDate	Index
Halt-F16	01/05/2023 3:39:00 AM	1
Halt-F16	01/05/2023 12:42:00 AM	2
Halt-F16	01/10/2023 2:55:00 AM	3
Halt-F17	01/11/2023 4:01:00 AM	4
Halt-F18	01/17/2023 12:00:00 PM	5
Fault-F35	01/17/2023 10:12:00 PM	6
Fault-F35	01/18/2023 1:58:00 AM	7

[lbendlin]

Your sample data is incorrect. Row 2 happens before Row 1.

Please provide usable sample data.

[shreep1]

Hello @lbendlin 

Row 2 happens to be 12:42:00 PM. Everything else the same! 

[lbendlin]

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "fc+7CoQwEAXQXwmpFe9MHI3TbSM2wvZiYb+l/r/IhCDq2h/uY5r8sPzWsqfGFx5UQSoGBxc0dAq4z+j8XPxTxFrzwb7jgyKYYhWxrDNqEyJDtYLuKLqk2lwIWKHt6pftgEEuDkr85mJyKjHfnHc=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Equipment = _t, BreakDownDate = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Equipment", type text}, {"BreakDownDate", type datetime}}
  ),
  #"Grouped Rows" = Table.Group(
    #"Changed Type",
    {"Equipment"},
    {
      {
        "Rows",
        each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type),
        type table [
          Equipment = nullable text,
          BreakDownDate = nullable datetime,
          Index = Int64.Type
        ]
      }
    }
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Grouped Rows",
    "NewRows",
    (k) =>
      Table.AddColumn(
        k[Rows],
        "MTBF",
        each if [Index] = 0 then null else [BreakDownDate] - k[Rows]{[Index] - 1}[BreakDownDate],
        type duration
      )
  ),
  #"Expanded NewRows" = Table.ExpandTableColumn(
    #"Added Custom1",
    "NewRows",
    {"BreakDownDate", "MTBF"},
    {"BreakDownDate", "MTBF"}
  ),
  #"Removed Other Columns" = Table.SelectColumns(
    #"Expanded NewRows",
    {"Equipment", "BreakDownDate", "MTBF"}
  ),
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Removed Other Columns",
    {{"BreakDownDate", type datetime}, {"MTBF", type duration}}
  )
in
  #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.