Forum Discussion
PowerBI Desktop
Hello PowerBI community
@Amit, @Greg , @tamerj1 , @lbendlin,
@amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler
@christinepayton @audreygerred
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?
21 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
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/1447523Hi Anonymous
opertime = DATEDIFF('EquipmentFailures'[CommissionedDate], 'EquipmentFailures'[BreakdownDate], DAY)
totalopertime = SUM('EquipmentFailures'[OperatingTime])
numberoffailures = COUNTROWS('EquipmentFailures')
meantimebetwfailures=divide(totalopertime,numberoffailures)
Hello lbendlin ,
Sorry about the delayed response. I was caught up on some other urgent things. But here is the sample dataset.
I will more than appreciate any of your help.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY5LCoAwDESvIlkLSWrqZ+nvFKX3v4YdqlUR6S5vHjMkBJqpJXEsyk60B3QFYhtoQeRZDZEAhgLwK6IpV5wlUGH1GeC33L996l8AvyOyp7dX//ufVf4bK/8p57v52feV/XMM+/EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equipment = _t, CommissionedDate = _t, BreakdownDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CommissionedDate", type date}, {"BreakdownDate", type date}}), process = (tbl)=> let #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 then null else [BreakdownDate]-#"Added Index"[BreakdownDate]{[Index]-1}), #"Grouped Rows1" = Table.Group(Table.SelectRows(#"Added Custom", each ([Custom] <> null)), {"Equipment"}, {{"Avg", each List.Average([Custom]), type duration}}) in if Table.RowCount(tbl)=1 then null else #"Grouped Rows1"[Avg]{0}, #"Grouped Rows" = Table.Group(#"Changed Type", {"Equipment", "CommissionedDate"}, {{"Rows", each _, type table [BreakdownDate=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "MTBF", each process([Rows]),type duration), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}) in #"Removed Columns"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 entire Source step with your own source.
Hello lbendlin
Can you please suggest this formula in PowerBI DAX instead of M-Query?
I don't understand M-Query that much. Plus, its a very hard programming language.
Thank you!
Hello powerbiexpert22
Thank you for the reply. In your algorithm, what is the CommissionedDate- Is it the date when the Asset was Commissioned or Started or is it when it last started after it failed? Because here, we are calculating mean time between failure.
Thanks,
