Forum Discussion
EARLIER chalenge
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
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] - __max
See 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.
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
Right, I was just trying to give you the general jist of where it was headed. I didn't actually test that code so it's a small miracle it worked at all without some type of syntax error! :) I only had a couple minutes to respond. Not surprising that you'd get an error for the first row outlier. Probably fixable by checking how many rows are returned using COUNTROWS.
Let me see if I can find some time to get your data loaded and write it out specifically with that data.
Thanks a lot Greg_Deckler and Chihiro. It worked very well.