Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to 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)
@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))
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)
User | Count |
---|---|
85 | |
81 | |
64 | |
53 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |