Forum Discussion
EARLIER chalenge
Hello Power friends,
I have a table with IT tickets like that:
Id_ticket, date, old_status, new_status,days_to_fix
100,01/01/2018,<blank>,"new ticket" ,0 // user report a problem opening a ticket
100,01/01/2018,"new ticket","working",0 // IT specialist start working on it
100,01/03/2018,"working","fixed", 2 // IT specialist solved it in two days
100,01/04/2018,"fixed" , "working",0 // user was not satisfied with solution and put the ticket on working status again
100,01/09/2018,"working","fixed", 5 // IT specialist got new solution in five days
100,01/10/2018,"fixed","closed", 0 // User accept this solution
The question is: "How can I calculate the "days_to_fix" collum in this example? Note that It has to be filled every time we have a "fixed" on new_status collum.
Thanks for your time and knowledge sharing.
Greg gave you the basic construct, and link explaining the concept.
You'll just need to change RETURN portion to only return result when [new_status]="fixed".
So...
Column = VAR __max = MAXX ( FILTER ( ALL ( 'Table' ), [Id_ticket] = EARLIER ( [Id_ticket] ) && [date] < EARLIER ( [date] ) && [new_status] = EARLIER ( [old_status] ) ), [date] ) RETURN IF ( [new_status] = "fixed", [date] - __max, 0 )Result
6 Replies
- Greg_Deckler
Community Champion
It is going to look something like:
Column = VAR __max = MAXX(FILTER(ALL('Table'),[Id_ticket]=EARLIER([Id_ticket])&&[date]<EARLIER([date])&&[new_status]=EARLIER([old_status])),[date]) RETURN [date] - __maxSee my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Thanks for your time Greg_Deckler, but, unfortunately, it didn't worked as expected. If you could check, I´ll be thankfull.
The new collum had a unknown value in the first line (the new ticket) and in the 2nd "working" line.
Thanks.
- Chihiro
Solution Sage
Greg gave you the basic construct, and link explaining the concept.
You'll just need to change RETURN portion to only return result when [new_status]="fixed".
So...
Column = VAR __max = MAXX ( FILTER ( ALL ( 'Table' ), [Id_ticket] = EARLIER ( [Id_ticket] ) && [date] < EARLIER ( [date] ) && [new_status] = EARLIER ( [old_status] ) ), [date] ) RETURN IF ( [new_status] = "fixed", [date] - __max, 0 )Result
