Forum Discussion
Extract Ticket Status from Noncontiguous Entries
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!
2 Replies
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/339586.
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 - __PreviousGreg_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.
