Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
checkner
Frequent Visitor

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:

checkner_0-1696933853579.png

 

I would be very grateful if anyone can help.

 

Kind regards

Christoph

1 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. 

vyiruanmsft_0-1697525669913.png

Best Regards

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

View solution in original post

4 REPLIES 4
checkner
Frequent Visitor

@rubayatyasmin Thanks! Now I get this error message:

checkner_0-1696937221888.png

 

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

 

Thanks


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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. 

vyiruanmsft_0-1697525669913.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rubayatyasmin
Super User
Super User

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

 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.