Forum Discussion

checkner's avatar
checkner
Frequent Visitor
2 years ago
Solved

Average time spent in status

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 IDApplication IDApplication StatusApplication Status CategoryCreated Date (Timestamp)Is Current StatusSkipped Status
3003842388666Personal AssessmentASSESSMENT22.05.2023 06:21 Skipped by user
3003842388666Cultural fit assessmentASSESSMENT22.05.2023 06:21 Skipped by user
3003842388666Manager ReviewASSESSMENT22.05.2023 06:21 Not Skipped
3003842388666Rejected - Direct RejectedCLOSEDY22.05.2023 09:23YESNot Skipped
3003842388666ScreeningSCREENING22.05.2023 06:21 Skipped by user
3003842388666DefaultAPPLIED19.05.2023 14:34 Not Skipped
3003842388666Recruiter InterviewSCREENING22.05.2023 06:21 

Skipped by user

1234561234567DefaultAPPLIED18.05.2023 13:27 

Not Skipped

1234561234567Recruiter InterviewSCREENING20.05.2023 06:21 

Not Skipped

1234561234567Manager ReviewASSESSMENT22.05.2023 06:21 YESNot 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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

4 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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()
)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
      rubayatyasmin
      Icon for Community Champion rankCommunity Champion
      2 years ago

      checkner sorry I had lost this thread. You can create an Calculated Column as suggested by Anonymous . 

       

      Thanks