How to compare value from conscutives rows and only for a same value ?

[Bruno_M]

Hi everyone,

I am facing a problem with a new report I am working on and hope to find some help oer some ideas here 😊

My source is an excel file and the data is about employee data :

Each time a person is hired, a new line is created in the Excel file, mentionning, among other informations 

- the start date

- the end date

- and an employee ID

A same person can be mentioned several time (vacation work for example).

Once my file is imported, I sort it by employee code, then by date, and get something like that :

What I am trying to do :

if the employee ID is the same, I would like to compare the 'start date' of the underneath row against the 'end date' of the row above.

In example given I would like to compare :

A2 vs nothing

A3 vs B2 (A3>B2)

A4 vs B3 (A4>B3)

A5 vs B4 (A5>B4)

A6 vs B5 (A6>B5)

A7 vs nothing (new Employee ID)

A8 vs B7 (A8>B7)

A9 vs B8 (A9>B8)

A10 vs nothing (new Employee ID)

and so on

I thought, as a first step, about grouping my data on the Employee ID column and then indexing it, but actualy, I dont know if it the right thing to do or what could be the next steps 😅

Thank you for you help !

Regards

[Bruno_M · Accepted solution]

HI @amitchandak,

I thank you for your help and for your time 😊

Unfortunately, the way you told me didn't work : it worked for some of the rows but some of them didn't get the right value.

I manage to solve my problem adding a row number by group (group based on the Employee ID) and then using the EARLIER function that I didn't knew about.

Regards

[amitchandak]

@Bruno_M , check if this can help

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...

To get end date of last row for same employee you can use

a new column =

var _max = maxx(filter(Table, [Employee ID] = earlier( [Employee ID]) && [End Date] < earlier( [End Date] )), [End Date] )

return

if([Start Date] > [End Date], 1, 0)