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 )