How to calculate Date diff with data setup

[SamBrown17]

Hi Team,

I have the example data below,

I have a database full of record types which are jobs in my system, I want to calculate the average time or date difference between a set sequence of the work flow.  so for the below I want to know the average time it take to go from Book to Scheduled and so on.

How could I go about this? Is it through a measure? or Reconfige of the data 

[v-henryk-mstf]

Hi @SamBrown17 ,

Whether the advice given by @Greg_Deckler  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[Greg_Deckler]

@SamBrown17 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/3395....
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

[SamBrown17]

@Greg_Deckler   Your article was insightful however I think my situation differs slightly, My table snippit is a database of jobs and the jobs run as a process, so lineal,  The example I have shared is a quote process, So a Job would start at Book, the team member would call to book in the Quote, and that is "Scheduled", Then the Team member attends and it changes to site attended, then the quote is submitted and the status changes again to awaiting insurer.  So I want to be able to report on those statuses, as the metric ber team member assisgned to those jobs.  So on average as a company we take this long to book a quote, and this long to attend, and this long to submit.

Does that make sense?

So I have a list of users that I set a relationship up with the Record ID, and I want to have performance meetings and say, OK "Team member A" on average this is your job transitions times.

[Greg_Deckler · Accepted solution]

@SamBrown17 Right, it's the same principle but yours will look something like this:

Column = 
  VAR __record = [record_id]
  VAR __type = [event_type]
  VAR __current = [created_at]
  VAR __currentStatus = [To (Status)]
  VAR __previous = 
    IF(__type = "Job status updated",
      SWITCH(__currentStatus,
        "Scheduled",MAXX(FILTER('Table',[record_id] = __record && [From (Status) = "Book" && [To (Status)] = BLANK(),[created_at]),
        "Site Attended",MAXX(FILTER('Table',[record_id] = __record && [From (Status) = "Book" && [To (Status)] = "Scheduled",[created_at])
      )
    )
RETURN
  __current - __previous

[SamBrown17]

Late to come back but I cannot find out why, but when I try and finished this it results in "The end of the input was reached" and it puts 3 closed brackets on the end every time I hit enter

[SamBrown17]

@Greg_Deckler 

[mh2587]

VAR CurrentDate = CALCULATE ( MAX(Table[Created_at] ),
ALL( 'Table'[Record_ID] )

)

var LastDate = CALCULATE(
MAX(Table[Created_at]),
ALL( 'Table'[Record_ID] ),

Table[Created_at] < CurrentDate )

VAR DD = DATEDIFF ( LastDate,CurrentDate, DAY )
RETURN DD