A measure to enable me to filter records by a certain status, but include all records prior

[SabrinaQ]

Hopefully I can explain this well enough to make sense. I have an ever growing HR data set where I'm trying to just filter down to those candidates that have been hired. Where I need help is how do I also include all the records for those candidates prior to them hitting the hires status in order to measure the time they spent in the previous statuses. Long story short, I need a measure or calculated column to get average days for each step in the hiring process for all candidate and then just those that were hired within a month. The main fields I've been working with to figure this out are Application ID, Application Status, Create Date (for status).

[amitchandak]

@SabrinaQ , for each row you can get previous row and have date diff , in a new column

Diff =

var _max= maxx(filter(table, [ID] =earlier([ID]) && [Created Date] < earlier([Created Date]) ), [Created Date])

return

datediff(_max, [Created Date], day)

Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

[SabrinaQ]

Thank you for your response @amitchandak 

I'm not sure your solution is what I need, as I am already calculating the days in each status by doing something similar.

Days in Status Calc =

VAR DateThis = 'App Status Audit Trail'[Created Date (Timestamp)]

VAR DateNext =

CALCULATE(

MIN('App Status Audit Trail'[Created Date (Timestamp)]),

FILTER(

ALL('App Status Audit Trail'),

'App Status Audit Trail'[Application ID]=EARLIER('App Status Audit Trail'[Application ID]) &&

'App Status Audit Trail'[Application Status]<>EARLIER('App Status Audit Trail'[Application Status]) &&

'App Status Audit Trail'[Created Date (Timestamp)]>EARLIER('App Status Audit Trail'[Created Date (Timestamp)])

)

)

RETURN

IF(

NOT ISBLANK(DateNext),

MAX(

COUNTROWS(CALENDAR(DateThis, DateNext))- 1,

0

))

To find the last status step (09 HIRED) for an Application ID and capture all the previous records for that Application ID, I created a new column that I use to filter. 

tbl Max Status = CALCULATE(MAX('App Status Audit Trail'[Step]), ALLEXCEPT('App Status Audit Trail','App Status Audit Trail'[Application ID]))

The problem I ran into with this, is that sometimes the  status will be accidently be moved to 10 Disqualified. This causes me to filter out many people that have actually been hired because the max status will be disqualified. 

This is where I figured I need to create a different formula that finds all of the Application IDs that have hit the Hired status and assign it the new column that way instead, but can't figure out how to do it.

Open to suggestions, of course! 🙂