Forum Discussion
How to calculate Date diff with data setup
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
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
7 Replies
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 DDSamBrown17 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 - __PreviousGreg_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.
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
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
