Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
stefanycheck
Frequent Visitor

Start and end time of change of status

Hi! 

I have a table with two different status that look like this:

 

DateStatusEarliestIndexindex
8/8/18 14:00Status 100
8/8/18 14:01Status 111
8/8/18 14:02Status 112
8/8/18 14:03Status 113
8/8/18 14:04Status 114
8/8/18 14:05Status 255
8/8/18 14:06Status 256
8/8/18 14:07Status 257
8/8/18 14:08Status 188
8/8/18 14:09Status 189
8/8/18 14:10Status 1810
8/8/18 14:11Status 1811
8/8/18 14:12Status 21212
8/8/18 14:13Status 21213
8/8/18 14:14Status 21214
8/8/18 14:15Status 21215
8/8/18 14:16Status 11616
8/8/18 14:17Status 11617
8/8/18 14:18Status 11618
8/8/18 14:19Status 11619

 

This EarliestIndex part is to make it so that I can count everytime a status changes. 

 

And I want it to look like this:

 

DateStatusEarliestIndexindexStartsEnds
8/8/18 14:00Status 1108/8/18 14:008/8/18 14:04
8/8/18 14:01Status 1118/8/18 14:008/8/18 14:04
8/8/18 14:02Status 1128/8/18 14:008/8/18 14:04
8/8/18 14:03Status 1138/8/18 14:008/8/18 14:04
8/8/18 14:04Status 1148/8/18 14:008/8/18 14:04
8/8/18 14:05Status 2658/8/18 14:058/8/18 14:07
8/8/18 14:06Status 2668/8/18 14:058/8/18 14:07
8/8/18 14:07Status 2678/8/18 14:058/8/18 14:07
8/8/18 14:08Status 1988/8/18 14:088/8/18 14:11
8/8/18 14:09Status 1998/8/18 14:088/8/18 14:11
8/8/18 14:10Status 19108/8/18 14:088/8/18 14:11
8/8/18 14:11Status 19118/8/18 14:088/8/18 14:11
8/8/18 14:12Status 213128/8/18 14:128/8/18 14:15
8/8/18 14:13Status 213138/8/18 14:128/8/18 14:15
8/8/18 14:14Status 213148/8/18 14:128/8/18 14:15
8/8/18 14:15Status 213158/8/18 14:128/8/18 14:15
8/8/18 14:16Status 117168/8/18 14:168/8/18 14:19
8/8/18 14:17Status 117178/8/18 14:168/8/18 14:19
8/8/18 14:18Status 117188/8/18 14:168/8/18 14:19
8/8/18 14:19Status 117198/8/18 14:168/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.

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors