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.
Hi!
I have a table with two different status that look like this:
Date | Status | EarliestIndex | index |
8/8/18 14:00 | Status 1 | 0 | 0 |
8/8/18 14:01 | Status 1 | 1 | 1 |
8/8/18 14:02 | Status 1 | 1 | 2 |
8/8/18 14:03 | Status 1 | 1 | 3 |
8/8/18 14:04 | Status 1 | 1 | 4 |
8/8/18 14:05 | Status 2 | 5 | 5 |
8/8/18 14:06 | Status 2 | 5 | 6 |
8/8/18 14:07 | Status 2 | 5 | 7 |
8/8/18 14:08 | Status 1 | 8 | 8 |
8/8/18 14:09 | Status 1 | 8 | 9 |
8/8/18 14:10 | Status 1 | 8 | 10 |
8/8/18 14:11 | Status 1 | 8 | 11 |
8/8/18 14:12 | Status 2 | 12 | 12 |
8/8/18 14:13 | Status 2 | 12 | 13 |
8/8/18 14:14 | Status 2 | 12 | 14 |
8/8/18 14:15 | Status 2 | 12 | 15 |
8/8/18 14:16 | Status 1 | 16 | 16 |
8/8/18 14:17 | Status 1 | 16 | 17 |
8/8/18 14:18 | Status 1 | 16 | 18 |
8/8/18 14:19 | Status 1 | 16 | 19 |
This EarliestIndex part is to make it so that I can count everytime a status changes.
And I want it to look like this:
Date | Status | EarliestIndex | index | Starts | Ends |
8/8/18 14:00 | Status 1 | 1 | 0 | 8/8/18 14:00 | 8/8/18 14:04 |
8/8/18 14:01 | Status 1 | 1 | 1 | 8/8/18 14:00 | 8/8/18 14:04 |
8/8/18 14:02 | Status 1 | 1 | 2 | 8/8/18 14:00 | 8/8/18 14:04 |
8/8/18 14:03 | Status 1 | 1 | 3 | 8/8/18 14:00 | 8/8/18 14:04 |
8/8/18 14:04 | Status 1 | 1 | 4 | 8/8/18 14:00 | 8/8/18 14:04 |
8/8/18 14:05 | Status 2 | 6 | 5 | 8/8/18 14:05 | 8/8/18 14:07 |
8/8/18 14:06 | Status 2 | 6 | 6 | 8/8/18 14:05 | 8/8/18 14:07 |
8/8/18 14:07 | Status 2 | 6 | 7 | 8/8/18 14:05 | 8/8/18 14:07 |
8/8/18 14:08 | Status 1 | 9 | 8 | 8/8/18 14:08 | 8/8/18 14:11 |
8/8/18 14:09 | Status 1 | 9 | 9 | 8/8/18 14:08 | 8/8/18 14:11 |
8/8/18 14:10 | Status 1 | 9 | 10 | 8/8/18 14:08 | 8/8/18 14:11 |
8/8/18 14:11 | Status 1 | 9 | 11 | 8/8/18 14:08 | 8/8/18 14:11 |
8/8/18 14:12 | Status 2 | 13 | 12 | 8/8/18 14:12 | 8/8/18 14:15 |
8/8/18 14:13 | Status 2 | 13 | 13 | 8/8/18 14:12 | 8/8/18 14:15 |
8/8/18 14:14 | Status 2 | 13 | 14 | 8/8/18 14:12 | 8/8/18 14:15 |
8/8/18 14:15 | Status 2 | 13 | 15 | 8/8/18 14:12 | 8/8/18 14:15 |
8/8/18 14:16 | Status 1 | 17 | 16 | 8/8/18 14:16 | 8/8/18 14:19 |
8/8/18 14:17 | Status 1 | 17 | 17 | 8/8/18 14:16 | 8/8/18 14:19 |
8/8/18 14:18 | Status 1 | 17 | 18 | 8/8/18 14:16 | 8/8/18 14:19 |
8/8/18 14:19 | Status 1 | 17 | 19 | 8/8/18 14:16 | 8/8/18 14:19 |
So I can make some kind of report with it, showing the time each change on status began and each time it ended.
Solved! Go to Solution.
I have a feeling of Deja Vu
Try this Column
Start = CALCULATE ( MIN ( Table1[Date] ), FILTER ( Table1, [EarliestIndex] = EARLIER ( [EarliestIndex] ) ) )
and this one
End = CALCULATE ( MAX ( Table1[Date] ), FILTER ( Table1, [EarliestIndex] = EARLIER ( [EarliestIndex] ) ) )
I have a feeling of Deja Vu
Try this Column
Start = CALCULATE ( MIN ( Table1[Date] ), FILTER ( Table1, [EarliestIndex] = EARLIER ( [EarliestIndex] ) ) )
and this one
End = CALCULATE ( MAX ( Table1[Date] ), FILTER ( Table1, [EarliestIndex] = EARLIER ( [EarliestIndex] ) ) )
Hhahahaha indeed! And thans a lot for your help again, it worked just the way I wanted!!!
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |