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 @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.