Max and Min by Item, status and dates

[Jacqee]

Hello,

I have a limited data set as an example which shows items as they go through different statuses.  I'm trying to get to who (User) worked each item (UID) from the last Assigned date to the first Passed or Failed date to determine how long it took to work each item with removing any time the item spent in an Awaiting status.

I started by trying to get the dates for the statuses in new columns but didn't know how to get the min & max since I only want the last Assigned date before the first Pass or Fail dates.  Then not sure how to build out removing time. 

Suggestions on how to do this?

[Anonymous]

Hi @Jacqee ,

Thanks for the reply from @vanessafvg .

First, please note the format of the Status Date column and make sure it is in Date/time format after correcting it.

Create two Calculation columns to calculate the last Assigned Date and the first Pass/Fail Date.

LastAssignedDate = 
CALCULATE(
    MAX('Table'[Status Date]),
    FILTER(
        'Table'.
        'Table'[UID] = EARLIER('Table'[UID]) &&
        'Table'[Status Desc] = "ASSIGNED" &&
        'Table'[Status Date] < 
            CALCULATE(
                MIN('Table'[Status Date]),
                FILTER(
                    'Table'.
                    'Table'[UID] = EARLIER('Table'[UID]) &&
                    ('Table'[Status Desc] = "PASSED" || 'Table'[Status Desc] = "FAILED")
                )
            )
    )
)

FirstPassedFailedDate = 
CALCULATE(
    MIN('Table'[Status Date]),
    FILTER(
        'Table'.
        'Table'[UID] = EARLIER('Table'[UID]) &&
        ('Table'[Status Desc] = "PASSED" || 'Table'[Status Desc] = "FAILED")
    )
)

Create a measure that calculates the Datediff and subtracts the wait time.

TimeTakenExcludingAwaiting = 
VAR _currentID = MAX('Table'[UID])
var _A =
CALCULATE(MAX('Table'[Status Date]),FILTER(ALLSELECTED('Table'),'Table'[Status Desc]="AWAITING CLARIFICATION"&&'Table'[UID]=_ currentID))
RETURN
IF(
    _A<>BLANK(),DATEDIFF(
    MAX('Table'[LastAssignedDate]),
    MAX('Table'[FirstPassedFailedDate]),
    DAY
)-DATEDIFF(MAX('Table'[LastAssignedDate])),_A,DAY))

The page visualization is shown below:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Jacqee]

Also is there a way to limit the count to just one day per day.  So if the UID goes into an Awaiting status more than once per day that it only counts it once and not multiple times?

[Jacqee]

This is awesome!  How would I add in additional "Awaiting" criteria like "Awaiting Update" and "Awaiting Review"?  I'm assuming it's in the measure... but mixing the Awaiting 'or's with the && UID throws me off.

[vanessafvg]

can you provide the data in text format?

[Jacqee]

Thank you!

Audit IndexUIDStatusStatus DateUserStatus Desc

617777	386020	6	04/02/2024 11:29AM	310575	ASSIGNED
617778	386020	6	04/02/2024 11:30AM	262462	ASSIGNED
617807	386020	47	04/02/2024 20:19	262462	WAITING ON REFRESH
618671	386020	4	04/13/202411:20	262462	AWAITING CLARIFICATION
618971	386020	26	04/24/2024 8:33	262462	ACTING OUT
619040	386020	2	04/24/2024 14:24	262462	IN-PROGRESS
619111	386020	30	04/25/2024 10:53	262462	DTU LEAD QUESTION
619211	386020	2	04/26/2024 13:47	262462	IN-PROGRESS
619331	386020	1	04/29/2024 16:41	262462	PASSED
618034	386021	6	04/03/2024 7:31	27654	ASSIGNED
618037	386021	6	04/08/2024 7:35:07AM	270378	ASSIGNED
618051	386021	6	04/03/2024 10:46	267573	ASSIGNED
618072	386021	47	04/03/2024 13:40	267578	WAITING ON REFRESH
618641	386021	4	04/18/2024 9:23:17AM	267573	AWAITING CLARIFICATION
619042	386021	26	04/24/2024 14:29	267573	ACTING OUT
619150	386021	2	04/25/2024 15:52	267578	IN-PROGRESS
619630	386021	1	05/02/202416:32	267573	PASSED
618054	386022	6	04/03/2024 10:47	315575	ASSIGNED
618063	386022	47	04/03/202411:23	315575	WAITING ON REFRESH
618559	386022	7	04/17/2024 10:29	315575	QUESTION
618604	386022	12	04/17/2024 17:32	87377	RETURNED
618609	386022	47	04/13/2024 6:37	315575	WAITING ON REFRESH
618630	386022	4	04/18/2024 9:10:47AM	315575	AWAITING CLARIFICATION
618871	386022	26	04/23/2024 8:26	315575	ACTING OUT
618924	386022	2	04/23/2024 14:17	315575	IN-PROGRESS
619144	386022	3	04/25/2024 15:05	315575	FAILED
619202	386022	12	04/26/2024 12:24	87877	RETURNED
619209	386022	2	04/26/2024 13:04	315575	IN-PROGRESS
619241	386022	7	04/29/2024 7:23	315575	QUESTION
619305	386022	12	04/29/2024 13:23	87377	RETURNED
619307	386022	27	04/29/2024 13:37	315575	IN LINE TESTING
619308	386022	2	04/29/2024 13:37	315575	IN-PROGRESS
619368	386022	1	04/30/2024 9:35	315575	PASSED