(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 want to visualize it in PowerBI. I have the following table:

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

 

[SanjayGMusafir]

Check this - Column H & I

Book2
	B	C	D	E	F	G	H	I
1	Application ID	Application Status	Application Status Category	Created Date (Timestamp)	Is Current Status	Skipped Status	Until	Time
2	123456	Default	APPLIED	5/19/23 14:34		Not Skipped	5/22/23 6:21	2 Days 15:47 Hrs
3	123456	Personal Assessment	ASSESSMENT	5/22/23 6:21		Skipped by user
4	123456	Cultural fit assessment	ASSESSMENT	5/22/23 6:21		Skipped by user
5	123456	Manager Review	ASSESSMENT	5/22/23 6:21		Not Skipped	5/22/23 9:23	0 Days 03:02 Hrs
6	123456	Screening	SCREENING	5/22/23 6:21		Skipped by user
7	123456	Recruiter Interview	SCREENING	5/22/23 6:21		Skipped by user
8	123456	Rejected - Direct Rejected	CLOSED	5/22/23 9:23	YES	Not Skipped	5/22/23 9:23	108 Days 05:11 Hrs
Sheet1

Cell Formulas
Range	Formula
H2:H8	H2	=IF(G2="Skipped by user","",LOOKUP(1,1/(A2:$A$8=A2)*(B2:$B$8=B2)*(G2:$G$8="Not Skipped"),E2:E8))
I2:I8	I2	=IFS(H2="","",F2="Yes",INT(NOW()-E2)&" Days "&TEXT(NOW()-E2-INT(NOW()-E2),"HH:MM")&" Hrs",TRUE,INT(H2-E2)&" Days "&TEXT(H2-E2-INT(H2-E2),"HH:MM")&" Hrs")

 

[checkner]

Unfortunately it doesn't work, if I have several data sets, Excel returns wrong values. Is it also possible to implement this directly in DAX?

 

[SanjayGMusafir]

Unfortunately it doesn't work, if I have several data sets, Excel returns wrong values. Is it also possible to implement this directly in DAX?

I'm unfamiliar with Power Query or DAX

Someone expert at it would help you with this.

 

[checkner]

Is there anybody with an idea/solution?