Average Time in Status

[checkner]

Hello,

I have an applicant workflow and would like to measure how long a candidate has spent in which status (per job and average across all jobs). I have the following table:

Job Req ID	Application ID	Application Status	Application Status Category	Created Date (Timestamp)	Is Current Status	Skipped Status
300384	2388666	Personal Assessment	ASSESSMENT	22.05.2023 06:21		Skipped by user
300384	2388666	Cultural fit assessment	ASSESSMENT	22.05.2023 06:21		Skipped by user
300384	2388666	Manager Review	ASSESSMENT	22.05.2023 06:21		Not Skipped
300384	2388666	Rejected - Direct Rejected	CLOSED	22.05.2023 09:23	YES	Not Skipped
300384	2388666	Screening	SCREENING	22.05.2023 06:21		Skipped by user
300384	2388666	Default	APPLIED	19.05.2023 14:34		Not Skipped
300384	2388666	Recruiter Interview	SCREENING	22.05.2023 06:21		Skipped by user

If the status is "Skipped by user", the applicant was not in this status, if it is "not skipped", the applicant was in this status. For the case shown, I would want to know how long the applicant was in the status "Default" and "Manager Review". If "CLOSED", then I do not need any time.

I would be very grateful if anyone can help.

Kind regards

Christoph

[checkner]

Anybody there with a solution for this?

[v-stephen-msft]

Hi @checkner ,

Could you please tell me what's the expected results according your sample data provided?

And this is the Power Query forum, You want a solution in Power Query, not a solution for DAX in Power BI Destkop, right?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[checkner]

Hi @v-stephen-msft

I would like to evaluate for each workflow step how long the candidates spent on average in which status, then break this down for each position. At the end, I would like to display the average value (per step) in my recruiting dashboard as a card and/or diagram. 

I am therefore not sure whether Power Query or PowerBI Desktop is the right solution.