Forum Discussion

brokencornets's avatar
brokencornets
Icon for Helper IV rankHelper 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...
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity 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
    Icon for Helper IV rankHelper 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!