Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |