Forum Discussion

brokencornets's avatar
brokencornets
Helper IV
8 years ago
Solved

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 f...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
brokencornets
Helper IV
8 years ago

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:

 

StatusTimestampActivityCrewEnd Timestamp
Travel12/02/2018 09:3322John Doe12/02/2018 09:34
Working12/02/2018 09:3422John Doe12/02/2018 15:58
Suspended12/02/2018 15:5822John Doenull
Travel13/02/2018 07:553John Doe13/02/2018 07:59
Working13/02/2018 07:593John Doe13/02/2018 15:46
Suspended13/02/2018 15:463John Doenull
Travel14/02/2018 08:2022John Doe14/02/2018 08:20
Working14/02/2018 08:2022John Doe14/02/2018 11:44
Complete14/02/2018 11:4422John Doenull
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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 )
      • brokencornets's avatar
        brokencornets
        Helper IV
        8 years ago

        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!