topic Re: Calculate completed operation and date difference in DAX Commands and Tips

Calculate completed operation and date difference

Anonymous — Tue, 20 Sep 2022 23:04:20 GMT

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

Re: Calculate completed operation and date difference

Greg_Deckler — Tue, 20 Sep 2022 23:52:08 GMT

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

Re: Calculate completed operation and date difference

Anonymous — Wed, 21 Sep 2022 00:10:43 GMT

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

Re: Calculate completed operation and date difference

Greg_Deckler — Wed, 21 Sep 2022 00:17:30 GMT

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?

Re: Calculate completed operation and date difference

Anonymous — Wed, 21 Sep 2022 00:19:51 GMT

Never mind, I figured it out, thanks so much for your help!

Re: Calculate completed operation and date difference

Anonymous — Wed, 21 Sep 2022 15:00:10 GMT

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

Re: Calculate completed operation and date difference

Anonymous — Wed, 21 Sep 2022 15:40:08 GMT

Greg_Deckler

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

Re: Calculate completed operation and date difference

Anonymous — Wed, 21 Sep 2022 16:16:32 GMT

Hi,

Probably need to add some flexibility as there are different text for starts (i.e., 010 Start, 010 Begin). All starts operations start with "010". Thank you.

Victor

Re: Calculate completed operation and date difference

Anonymous — Thu, 22 Sep 2022 03:46:30 GMT

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

Re: Calculate completed operation and date difference

Anonymous — Thu, 22 Sep 2022 18:40:23 GMT

Adding && doesn't seem to work. Appreciate any insight, thank you.

Completed? =
VAR __serial = [Serial #]
VAR __table = FILTER('table', [Serial #] = __serial && [Operation] = "10" && [Operation] = "30")
RETURN
IF(COUNTROWS(__table)+0 > 0,"Y","N")