timestamps between start and end - by person

[buchabl]

I have a table that shows a time value column and a time attribute column (start/stop, 2nd start/2nd stop) for samples that were extracted manually from a greater dataset. The samples are measuring the time it takes to wash a car, where there may be gaps of time they stop working and then start working on the same car again at a later time (hence why there is a 2nd start/2nd end or 3rd start/3rd end). The start and end times were recorded and each car that was washed was given a number value to show that it is a sample that was recorded (column with example number). I am trying to populate the number value (the example number) for ALL the timestamps between the start and end points so that the different tasks of washing a car can also be observed not just the start and end task (for each washer that had samples collected). This will have to be done by references another table (screenshot 2) that has all the time recorded but by activity; using start_time and end_time. I am wanting to see if any of the timestamps from the samples is = to a single activies start or end time OR if a samples timestamp falls between a single activities start_time and end_time for that washer. If this condition is true the corresponding example number should be populated in that row in a new column. Lets say we see an example start timestamp match a timestamp in the activity table, I want the activities between the start and end points for that example to populate the new column with that example number until it reaches the end timestamp for the example.

Screenshot 1 - examples table

screenshot 2 - total activities tablescreenshot 2 - activities table

 

[buchabl]

here is a better screenshot of screenshot 1screenshot 1 the example table @collinq 

[buchabl]

@collinq 

to ellaborate, if my first example from screenshot one in my original message said start time was 6:04:45 (third from bottom, end time column in screen shot 2 (known as activities or tasks table which i included again in this reply) a new column would be created and show the number "1" in the 3rd from bottom row, then if the end time in example 1 said 6:15:56 then each row up to that point would be populated with the number "1" in the new column

[buchabl]

also this activities table has a column for "washer" so you can identify who is doing what tasks

[buchabl]

also, the cars are being washed by multiple different people so we are wanting to distinguish what times were spent doing tasks that make up washing a car and which worker was washing the car at that time 

[collinq]

Hey @buchabl ,

I think I am starting to understand better - if you are going to be comparing rows against each other and against another table to get the start dates and end dates for activities based on a washer and/or car then you are going to probably need to be using Dax. 

That said, there is an M solution I found here:

Calculate The Difference Between Consecutive Rows Grouped By Column (c-sharpcorner.com)

But, this Dax solution may work more effectively for you:

Solved: Calculate difference between rows - Microsoft Power BI Community

[buchabl]

@collinq I know what the time duration between start and end points are to wash an entire car but what I am trying to figure out is the tasks that occur between the start and end points; meaning what smaller tasks (like scrubbing, rinsing, drying) need to happen to accomplish the overall task (washing a car)... each task is made up of  a start and end timestamp. I want to match my example timestamps with the start and end points from the activities table because there is no distinction in the data showing the periods of time where workers are actively washing cars or not; it is just a list of timestamps and tasks but no categorizatin of what tasks are put together to accomplish the goal (washing a car).

[buchabl]

screen shot 1 in my original post is not an accurate representation of the table and it is hard to see so I  posted a corrected version of screenshot 1 in the replies

[collinq]

Hi @buchabl ,

I am not quite sure which of the following scenarios you are trying - so I will take a run at both.

If you are trying to get the difference between the Start/End times you can use the duration command in M.  Yo ucan create a custom column that does the following:

[TaskFinishDate]-[TaskStartDate]

And then, change it to Duration:

Or, if you are looking to get the difference between EndDate1 and StartDate2 you can do the same process.

Does that help?