Forum Discussion
Comparing dates in adjacent rows
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!
5 Replies
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
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 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 )
