Forum Discussion
Calculate completed operation and date difference
Community ChampionAnonymous So, yes, as a column:
Completed? Column =
VAR __serial = [serial #]
VAR __table = FILTER('Table',[serial #] = __serial && [Operation #] = 30)
RETURN
IF(COUNTROWS(__table)+0 > 0,"Y","N")The second part is essentially MTBF, 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 - __Previous
Never mind, I figured it out, thanks so much for your help!
H Greg,
Appreciate the quick response, and it works based on the Operation #, your the man.
I tried substituting the Operation # with "010 Start" (It's actually text) , but could not get it to work. Is there something I need to do?
Many thanks!
Victor
- Greg_Deckler
Anonymous Hmm, hang on, what are the actual values for 10, 20 and 30 Operation #'s? Also, does a serial # need to have all three Operation # values to be present before it is considered completed or is a single "30" value sufficient?
Here's the extended data and desired output: serial # 4 has no Operation 10 so it's not complete.
Hope someone can help me. Thank you.
Serial # Operation # Date completed Completed? Op # 10-30 - Desired output 1 10 1/2/2022 Y 1 20 1/3/2022 Y 1 30 1/5/2022 Y 2 10 1/8/2022 Y 2 20 1/10/2022 Y 2 25 1/12/2022 Y 2 30 1/14/2022 Y 3 10 1/5/2022 N 3 20 1/9/2022 N 3 25 1/12/2022 N 4 20 1/10/2022 N 4 25 1/11/2022 N 4 30 1/12/2022 N