0

I am trying to calculate the mean time to failure for each asset in a job table. At the moment I calculate as follows;

Previous ID = CALCULATE(MAX('JobTrackDB Job'[JobId]),FILTER('JobTrackDB Job','JobTrackDB Job'[AssetDescriptionID]=EARLIER('JobTrackDB Job'[AssetDescriptionID]) && 'JobTrackDB Job'[JobId]<EARLIER('JobTrackDB Job'[JobId])))

Then I bring back the last finish time for the current job when the JobStatus is 7 (closed);

Finish Time = CALCULATE(MAX('JobTrackDB JobDetail'[FinishTime]),'JobTrackDB JobDetail'[JobId],'JobTrackDB JobDetail'[JobStatus]=7)

Then I bring back the previous jobs finish time where the JobType is 1 (Response rather than comparing it to maintenance calls);

Previous Finish = CALCULATE(MAX('JobTrackDB Job'[Finish Time]),FILTER('JobTrackDB Job','JobTrackDB Job'[AssetDescriptionID]=EARLIER('JobTrackDB Job'[AssetDescriptionID]) && 'JobTrackDB Job'[Finish Time]<EARLIER('JobTrackDB Job'[Finish Time]) && EARLIER('JobTrackDB Job'[JobTypeID])=1))

Then I calculate the Time between failure where I also disregard erroneous values;

Time between failure = IF([Previous Finish]=BLANK(),BLANK(),IF('JobTrackDB Job'[Date Logged]-[Previous Finish]<0,BLANK(),'JobTrackDB Job'[Date Logged]-[Previous Finish]))

Issue is that sometimes the calculation uses previous maintenance jobs even though I specified JobTypeID = 1 in the filter. Also, the current calculation does not take into account the time from the start of records to the first job for that asset and also from the last job till today. I am scratching my head trying to figure it out.

Any ideas???

Thanks, Brent

BrentA
  • 172
  • 8

1 Answers1

0

Some base measures:

MaxJobID := MAX( Job[JobID] )

MaxLogDate := MAX ( Job[Date Logged] )

MaxFinishTime := MAX (JobDetail[Finish Time])

Intermediate calculations:

ClosedFinishTime := CALCULATE ( [MaxFinishTime], Job[Status] = 7 )

AssetPreviousJobID := CALCULATE ( 
 [MaxJobID], 
 FILTER(
   ALLEXCEPT(Job, Table1[AssetDescriptionID]), 
   Job[JobId] < MAX(Table1[JobID])
 )
)

PreviousFinishTime: = CALCULATE ( [ClosedFinishTime], 
 FILTER(
   ALLEXCEPT(Job, Job[AssetDescriptionID]), 
   Job[JobId] < MAX(Job[JobID])
   && Job[JobType] = 1
 )
)

FailureTime := IF (
 ISBLANK([PreviousFinishTime]), 
 0,
 ( [MaxLogDate]-[PreviousFinishTime] )
)

This should at least get you started. If you want to set some sort of "first day", you can replace the 0 in the FailureTime with a calc like MaxLogDate - [OverallFirstDate], which could be a calculated measure or a constant.

For something that hasn't failed, you'd want to use an entirely different measure since this one is based on lookback only. Something like [Days Since Last Failure] which would just be (basically) TODAY() - [ClosedFinishTime]

Kyle Hale
  • 7,912
  • 1
  • 37
  • 58
  • Thanks for that! All works well up till the PreviousFinishTime which is bringing back dates that are after the 'current' job. Just trying to trace why that is... – BrentA May 21 '16 at 09:34
  • You're sure finish time is tied to JobID? No way JobID 6 has a later finish date than Job1D 7, etc? – Kyle Hale May 21 '16 at 16:15
  • Yes. The finish time is tied to JobID through the link to the JobDetail Table which has the finish times. That element works bringing back the current jobs finish time but not the previous finish time. JobID 6 can definitely have a finish time later than JobID7.I am thinking that the JobType filter is 'lost' in the calculation as it doesnt specifically relate to the earlier filter for each assetID. I think the first filter needs to act and then the JobType filter but thats not what is happening. – BrentA May 21 '16 at 23:43
  • So just to be 100% clear: the "previous job" is defined as the last jobID before the current jobID which is for the same asset whose jobType is 1? – Kyle Hale May 22 '16 at 21:34
  • CAn you provide some sample data from Job and JobDetail and an expected result? – Kyle Hale May 23 '16 at 18:06
  • As you can tell, it's hard to diagnose the issue without data, but those filters work fine on the fake data I created to match your problem. – Kyle Hale May 23 '16 at 18:08