Forum Discussion
How to calculate Date diff with data setup
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 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 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 - __previousLate 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