Trying to calculate MTBF

[mfa]

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

[amitchandak]

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

[mfa · Accepted solution]

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)