Average elapsed time for a job to change hands from one worker to others

[igonzalezb]

I would like to know the average time it takes for Jobs to change from Worker X to any other worker. Timestamp is here shown as an integer. The result should be (2+10)/2 = 6. See that JobID 3 hasn't changed from Worker X and should be ignored in the calculation.

JobID	Timestamp	Worker
2	15	C
3	12	X
1	8	A
1	4	B
1	3	C
2	5	X
1	2	B
2	1	A
1	0	X

Using this example, the logic behind the calculation is as follows:

1. For JobID = 1, X has the job at timestamp 0. Then JobID=1 changes worker at timestamp = 2. Therefore the time elapsed is 2.
2. For JobID = 2, X has the job at timestamp 5. Then JobID=2 changes worker at timestamp 15.  Therefore the time elapsed is 10.
3. For JobID = 3, X has the job at timestamp 12. This JobID hasn't changed worker since. Time elapsed is null.
4. Average of the 3, not counting null, is (2+10)/2 = 6.

[mahoneypat · Accepted solution]

Please try this measure expression.  It returns 6 for your example data.  If you want a more general solution, you can replace the FILTER(__summary, Jobs[Worker] = "X") with just __summary (to use it in a table for example to see the avg time for each employee.

Avg Job Change Time =
VAR __summary =
    ADDCOLUMNS (
        SUMMARIZE ( Jobs, Jobs[Worker], Jobs[JobID], Jobs[Timestamp] ),
        "@nexttimestamp", CALCULATE (
            MIN ( Jobs[Timestamp] ),
            ALL ( Jobs ),
            Jobs[Timestamp] > EARLIER ( Jobs[Timestamp] ),
            Jobs[JobID] = EARLIER ( Jobs[JobID] )
        ) + 0
    )
RETURN
    AVERAGEX (
        FILTER ( FILTER ( __summary, Jobs[Worker] = "X" ), [@nexttimestamp] > 0 ),
        [@nexttimestamp] - Jobs[Timestamp]
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ryan_mayu]

@igonzalezb 

could you please provide detailed logic on the calculation? how to get 10 and 2 in your formula?

[igonzalezb]

@ryan_mayu Please see my edit. Hope it helps. Thank you!