Average time spent 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	CLOSEDY	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
123456	1234567	Default	APPLIED	18.05.2023 13:27		Not Skipped
123456	1234567	Recruiter Interview	SCREENING	20.05.2023 06:21		Not Skipped
123456	1234567	Manager Review	ASSESSMENT	22.05.2023 06:21	YES	Not Skipped

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.

Example fpr average time in status:

I would be very grateful if anyone can help.

Kind regards

Christoph

[checkner]

@rubayatyasmin Thanks! Now I get this error message:

[rubayatyasmin]

@checkner sorry I had lost this thread. You can create an Calculated Column as suggested by @v-yiruan-msft . 

Thanks

[v-yiruan-msft · Accepted solution]

Hi @checkner ,

It seems that you are trying to create a measure not a calculated column. If you want to apply the formula which provided by @rubayatyasmin , you need to create a calculated column instead of measure. 

Best Regards

[rubayatyasmin]

Hi, @checkner 

Filter out rows where Skipped Status is "Skipped by user" and Application Status is "CLOSEDY" using Power Query Editor.

then use the below DAX to achieve your result

TimeSpent = 
IF(
    NOT(ISBLANK(
        CALCULATE(
            MIN(ApplicantStatus[Created Date (Timestamp)]),
            FILTER(
                ApplicantStatus,
                ApplicantStatus[Application ID] = EARLIER(ApplicantStatus[Application ID]) &&
                ApplicantStatus[Created Date (Timestamp)] > EARLIER(ApplicantStatus[Created Date (Timestamp)]) &&
                ApplicantStatus[Application Status] <> "Skipped by user" &&
                ApplicantStatus[Application Status] <> "CLOSEDY"
            )
        )
    )),
    (CALCULATE(
        MIN(ApplicantStatus[Created Date (Timestamp)]),
        FILTER(
            ApplicantStatus,
            ApplicantStatus[Application ID] = EARLIER(ApplicantStatus[Application ID]) &&
            ApplicantStatus[Created Date (Timestamp)] > EARLIER(ApplicantStatus[Created Date (Timestamp)]) &&
            ApplicantStatus[Application Status] <> "Skipped by user" &&
            ApplicantStatus[Application Status] <> "CLOSEDY"
        )
    ) - EARLIER(ApplicantStatus[Created Date (Timestamp)])) * 24,
    BLANK()
)