Extract Ticket Status from Noncontiguous Entries

[samnf]

Hello Community,

Thank you in advance for your time and support!

To put the matter into perspective, I am building a model to trace a certain project's life cycle and extract some metrics based on some other columns. I am interested in extracting the duration during which a certain project was "on-hold", however, if a certain projects status changes from "ongoing" to "on-hold" then there may be several days where this project does not receive an input. Essentially, I am looking for when a project changed to on hold and for how long it was on hold (till the next time it is logged as ongoing).

Consider this a sample set:

Take projects 1 and 2 as an example, the projects were placed on hold for a few days before resuming progress, bearing in mind that the project could still possibly have daily entries with on-hold status.

I have tried following a certain index method but with a full dataset it isn't as easy as the daily number of projects/tickets could differ, I tried approaching this using Dax measures to select a previous value but it would pick up the status of the earliest entry on the table.

Not sure if this describes the issue I'm facing, if there is anything I missed please do mention it, thank you again!

[Greg_Deckler]

@samnf See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[samnf]

@Greg_Deckler Hello Greg, thank you for your time, this is almost what I am looking for, to be more specific, when I implemented part of the calculated column I got the following outputs:

As seen here, project ID 3 has one of it's previous status' as On-Hold:

while it never got an on-hold entry, essentially for every entry, based on ID I'm interested in finding the previous status and previous entry date, keeping in mind that there might be more than a single day delay  between an entry and another (noncontinguous).

The follow up question is, instead of having this as a calculated column, how can I approach it as an added column on power query editor. Thank you again.