cancel
Showing results for
Did you mean:
Frequent Visitor

## Trying to calculate MTBF

here is my Data input:

 Date Truck Status 1-Jan 1234-TTA Active 2-Jan 1234-TTA Active 3-Jan 1234-TTA Active 4-Jan 1234-TTA Active 5-Jan 1234-TTA Active 6-Jan 1234-TTA Out of Service 7-Jan 1234-TTA Active 8-Jan 1234-TTA Active 9-Jan 1234-TTA Out of Service 10-Jan 1234-TTA Out of Service 11-Jan 1234-TTA Active 12-Jan 1234-TTA Out of Service 13-Jan 1234-TTA Active 14-Jan 1234-TTA Active 15-Jan 1234-TTA Active 16-Jan 1234-TTA Out of Service 17-Jan 1234-TTA Out of Service 18-Jan 1234-TTA Active 19-Jan 1234-TTA Active 20-Jan 1234-TTA Out of Service 21-Jan 1234-TTA Out of Service 22-Jan 1234-TTA Out of Service 23-Jan 1234-TTA Out of Service 24-Jan 1234-TTA Active 25-Jan 1234-TTA Out of Service 26-Jan 1234-TTA Out of Service 27-Jan 1234-TTA Active 28-Jan 1234-TTA Out of Service 29-Jan 1234-TTA Active 30-Jan 1234-TTA Active 31-Jan 1234-TTA Out of Service

acording the the data number of breakdowns would be 8 and number of operational days would be 17

is there a DAX formula that would easily go through a list such as this generate these numbers for me

1 ACCEPTED SOLUTION
Frequent Visitor

Amazing thank you so much, only adjustment to the Formula would be subtracting 1 from the result

new column =
var _max = maxx(filter(Table, [Truck] = earlier([Truck]) && [Date] <earlier([date]) && [Status] ="Out of Service"),[Date])
return
if( [Status] ="Out of Service" && not(isblank(_max)), datediff(_max, [Date], day)-1)

2 REPLIES 2
Super User

@mfa , you can create a column like and use that for avg

new column =
var _max = maxx(filter(Table, [Truck] = earlier([Truck]) && [Date] <earlier([date]) && [Status] ="Out of Service"),[Date])
return
if( [Status] ="Out of Service" && not(isblank(_max)), datediff(_max, [Date], day))

Frequent Visitor

Amazing thank you so much, only adjustment to the Formula would be subtracting 1 from the result

new column =
var _max = maxx(filter(Table, [Truck] = earlier([Truck]) && [Date] <earlier([date]) && [Status] ="Out of Service"),[Date])
return
if( [Status] ="Out of Service" && not(isblank(_max)), datediff(_max, [Date], day)-1)

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors