Start and end time of change of status

[stefanycheck]

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.

[Zubair_Muhammad · Accepted solution]

@stefanycheck

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

[stefanycheck]

@Zubair_Muhammad

Hhahahaha indeed! And thans a lot for your help again, it worked just the way I wanted!!!