cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Trying to calculate MTBF

here is my Data input:

DateTruckStatus
1-Jan1234-TTAActive
2-Jan1234-TTAActive
3-Jan1234-TTAActive
4-Jan1234-TTAActive
5-Jan1234-TTAActive
6-Jan1234-TTAOut of Service
7-Jan1234-TTAActive
8-Jan1234-TTAActive
9-Jan1234-TTAOut of Service
10-Jan1234-TTAOut of Service
11-Jan1234-TTAActive
12-Jan1234-TTAOut of Service
13-Jan1234-TTAActive
14-Jan1234-TTAActive
15-Jan1234-TTAActive
16-Jan1234-TTAOut of Service
17-Jan1234-TTAOut of Service
18-Jan1234-TTAActive
19-Jan1234-TTAActive
20-Jan1234-TTAOut of Service
21-Jan1234-TTAOut of Service
22-Jan1234-TTAOut of Service
23-Jan1234-TTAOut of Service
24-Jan1234-TTAActive
25-Jan1234-TTAOut of Service
26-Jan1234-TTAOut of Service
27-Jan1234-TTAActive
28-Jan1234-TTAOut of Service
29-Jan1234-TTAActive
30-Jan1234-TTAActive
31-Jan1234-TTAOut 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
Anonymous
Not applicable

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)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , 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))

Anonymous
Not applicable

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)

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors