PowerBI Desktop

[shreep1]

Hello PowerBI community 

@Amit, @Greg , @tamerj1 , @lbendlin,

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

@christinepayton @audreygerred 

@LukeB,

@Kedar_Pande 

@rajendraongole1 

@Ritaf1983 

@SamWiseOwl 

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

@lbendlin 

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, 

[shreep1]

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. 

[lbendlin]

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.

[shreep1]

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!

[lbendlin]

[shreep1]

Hello @lbendlin ,

This is very helpful! This algorithm is yielding correct results for the MTBF for individual Equipments when I am using the Stacked Column Chart like this:

But I am trying to get the MTBF for all the Equipments for each month- January, February, March, etc..If I put the MTBF on the Y-axis, BreakDate on the X-axis and Legend = Equipment, I get the MTBF for the individual equipments but not as a whole for all the Equipments. 

If I add the MTBF for all those individual Equipments, I am getting MTBF like 150, 200 for respective months whereas I am expecting value anywhere between 10-20 days. 

How do I get the MTBF for all those necessary Equipments for each respective months?

Thanks

[shreep1]

Hello @lbendlin ,

Adding to this question, I can neither write a Measure nor a Calculated Column inside of that table using Average Function for the MTBF33. Also, PowerBI also does not allow me to select SUM, AVERAGE, MIN, MAX dropdown from the MTBF33 shown in the visual above in the Y-axis. 

I think applying average for the MTBF for all those individual equipments for each of the respective month will solve this problem but I don't know how to go about solving it?

Thanks

[lbendlin]

How do I get the MTBF for all those necessary Equipments for each respective months?

I have no idea how this would logically even be possible.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[shreep1]

Equipment	Commissioned Date	BreakDown Date
A	02/01/2024	03/15/2024
B	01/01/2024	02/01/2024
C	01/15/2024	02/15/2024
A	02/01/2024	02/28/2024
B	02/20/2024	03/20/2024
C	03/01/2024	03/27/2024

Hello @lbendlin ,

I have added a dataset to recalculate the MTBF for February and March 2024. I am fine with your earlier formula of 

MTBF = var a = values('MergedTable'[BreakDate]) return if(HASONEVALUE(MergedTable[Equipment]),DIVIDE(maxx(a,[BreakDate])-minx(a,[BreakDate]),COUNTROWS(a)-1))

But this time, I want the MTBF for February and March of 2024 instead of individual equipments. 

Can you please suggest the DAX code in consistent with the formula above?

Thank you again! 

[lbendlin]

I want the MTBF for February and March of 2024

I don't know what that means.  Do you want the MTBF for equipments that were commissioned in these months?

[shreep1]

Hello @lbendlin ,

Yes, I want the MTBF on the Y-axis and Months on the X-axis. MTBF should include all the Equipments for that particular month. 

Thank you! 

[shreep1]

Hello @lbendlin ,

I am just following up to see if you have made any progress to figure it out?

Thanks

[lbendlin]

What does " for that particular month. "  mean?  Commissioned in that month? Having a failure in that month?

[shreep1]

Hello @lbendlin ,

Since we are calculating Mean Time Between Failure, I think we should use BreakDate for that particular month which should be the X-axis. 

Thanks,

[shreep1]

Hello @lbendlin ,

I am not sure how are you getting MTBF for Equipment A as 30.5 days and Equipment B as 24.50 days. Because, Equipment A has failed 3 times and the total operating time is 30+61+91 = 182 days, No of times it has failed = 3, So, MTBF should be 60.67 for Equipment A. 

Similarly, For Equipment B, the total operating time is 62+93+111 = 266 days and it also failed 3 times, so the MTBF for Equipment B should be 88.67 days. 

Can you please tell me your logic here? 

Thanks.

[lbendlin]

I guess you and I have a different understanding of what MTBF means.

Equipment A has failed three times.  That means it had two "times between failure".  MTBF can be computed by averaging each individual "time between failure"  or - simpler - by dividing the time from the earliest failure to the latest failure by two, resulting in 30.5 .  The "total operating time"  has no influence on the MTBF calculation.

[shreep1]

Hello @lbendlin 

Total Operating Time has direct relationship with Mean Time Between Failure. MTBF is the Total Operating Time/Number of Failures. 

Also, in the sample dataset, Equipment A has failed 3 times. Equipment B has also failed 3 times. So, the Number of Failures is equivalent to 3.

Please refer to the snapshot above. And we are trying to capture this in our DAX code. Can you please suggest an appropriate DAX code?

Thanks. 

[lbendlin]

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

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]

Equipment         CommissionedDate        BreakdownDate

A                        02/01/2016                      03/01/2016

B                         05/14/2010                      07/14/2010

C                         09/01/2024                     10/15/2024

D                         05/01/2024                      07/01/2024 E                          04/01/2024                      04/15/2024

A                          02/01/2016                     04/01/2016 B                          05/14/2010                     08/14/2010 C                          09/01/2024                     11/1/2024

A                          02/01/2016                     05/01/2016

B                          05/14/2010                      09/01/2010