Comparing dates in adjacent rows

[brokencornets]

Hi all

I'm new to Power BI and I'm trying to convert an Excel report. One thing I'm finding extremely difficult is comparing different rows. To give some context, the structure of my data is as follows:

Job Ref	Status	Timestamp	Activity	Crew
1111111	Travel	12/02/2018 09:33	22	John Doe
1111111	Working	12/02/2018 09:34	22	John Doe
1111111	Suspended	12/02/2018 15:58	22	John Doe
2222222	Travel	13/02/2018 07:55	3	John Doe
2222222	Working	13/02/2018 07:59	3	John Doe
2222222	Suspended	13/02/2018 15:46	3	John Doe
1111111	Travel	14/02/2018 08:20	22	John Doe
1111111	Working	14/02/2018 08:20	22	John Doe
1111111	Complete	14/02/2018 11:44	22	John Doe

So the issue with my data is that there is no 'end' timestamp - so in the above, the only way of knowing how long John Doe was in a 'Working' status is by getting the 'Suspended' timestamp. I'm currently using VBA to achieve this in Excel, but I'm struggling to get my head around DAX to be honest.

Looking at other solutions, I'm assuming I'm going to need EARLIER but nothing I'm trying is working so I hoped someone using my data would be able to offer a more bespoke solution? What I'm basically aiming to achieve is an extra column which will show the duration for every Travel and Working row.

Thanks for any help you can offer!

[vmakhija]

@brokencornets

Using your dataset, I created 3 measures as below -

WorkingTime =
CALCULATE ( MINX( FILTER(ShiftData,ShiftData[Status]="Working"),ShiftData[Timestamp]) , ALLEXCEPT ( ShiftData, ShiftData[Job Ref] ) )

SuspendedTime =
CALCULATE ( MAXX( FILTER(ShiftData,ShiftData[Status]="Suspended"),ShiftData[Timestamp]) , ALLEXCEPT ( ShiftData, ShiftData[Job Ref] ) )

TotalTimeSpent = [SuspendedTime] - [WorkingTime]

Here is how the data looks -

Check and let me know if it helps you.

Regards

[brokencornets]

Thanks for getting back to me vmakhija

Unfortunately it's not quite bring back the results I need, as it only brings back the working time and not the travelling time, and also doesn't take into account when jobs are completed, only suspended. It also only seems to look at one days worth of work, although that could be because it's only looking for suspended and not complete?

The ideal reasult I'm looking for is this:

Status	Timestamp	Activity	Crew	End Timestamp
Travel	12/02/2018 09:33	22	John Doe	12/02/2018 09:34
Working	12/02/2018 09:34	22	John Doe	12/02/2018 15:58
Suspended	12/02/2018 15:58	22	John Doe	null
Travel	13/02/2018 07:55	3	John Doe	13/02/2018 07:59
Working	13/02/2018 07:59	3	John Doe	13/02/2018 15:46
Suspended	13/02/2018 15:46	3	John Doe	null
Travel	14/02/2018 08:20	22	John Doe	14/02/2018 08:20
Working	14/02/2018 08:20	22	John Doe	14/02/2018 11:44
Complete	14/02/2018 11:44	22	John Doe	null

[Zubair_Muhammad]

Hi @brokencornets

Try this. First Add an Index Column in your Table using Query Editor

Then use this calculated column

Column =
VAR NextTimeSTamp =
    CALCULATE (
        MIN ( Table1[Timestamp] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Crew] ),
            Table1[Index] > EARLIER ( Table1[Index] )
        )
    )
VAR NextJobRef =
    CALCULATE (
        MIN ( Table1[Job Ref] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[Crew] ), Table1[Timestamp] = NextTimeSTamp )
    )
RETURN
    IF ( Table1[Job Ref] = NextJobRef, NextTimeSTamp )

[Zubair_Muhammad · Accepted solution]

@brokencornets

See the File attached

[brokencornets]

This looks great @Zubair_Muhammad, thanks!

Unfortunately when I try it in my file I get an error stating I don't have enough memory, so I'm probably going to need to strip back the amount of data I'm pulling into it, but if it works like it looks like it does it'll be awesome.

Thanks again!