Forum Discussion
Calculate completed operation and date difference
Hi,
I'm trying to calculate Completed? column based on serial #s, operations 10-30 = completed operation based on serial #.
Then calculate the date difference between completed serials (10 and 30 operations).
Is this possible in Power BI?
Thank you!
| serial # | Operation # | Date completed | Completed? Op # 10-30 |
| 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 |
9 Replies
- Greg_Deckler
Community Champion
Anonymous 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- 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?
Hi Greg,
To answer your question yes, the operations is noted to be completed starting with 010 Start and 030 Finish to finish.
So yes, I need to include the 010 Start to note the completion and not just add the 030. Is this possible? Thank you so much for your help.
Victor
