Date differences between dates with different status

[timcorke]

Good afternoon, I'm hoping you could help me.

For each individual [UID] there are different status changes [ACTIVITY_TYPE] made up of REQUESTED, RESPONDED, CLOSED, CANCELLED based on the progress of communication.

Each time the status changes a new row is created, timestamped marked with an [ACTIVITY_ID] and the [ACTIVITY_TYPE] changes accordingly. 
The status changes can go back and forth between REQUESTED and RESPONDED but there is only one CLOSED and/or CANCELLED.
The date change is marked in the same date field ACTIVITY DATE.

UID ACTIVITY_DATE ACTIVITY_TYPE
12345 01/01/2022 REQUESTED
12345 10/01/2022 REQUESTED
12345 11/01/2022 RESPONDED
12345 13/01/2022 REQUESTED
12345 22/01/2022 RESPONDED
12345 23/01/2022 CLOSED

I'm looking to get the time between the first REQUESTED, and the first RESPONDED, even though future status changes could follow.

Any pointers would be welcomed,

Tim

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Expected result: =
VAR firstrequesteddate =
    MINX (
        FILTER (
            Data,
            Data[UID] = MAX ( Data[UID] )
                && Data[ACTIVITY_TYPE] = "REQUESTED"
        ),
        Data[ACTIVITY_DATE]
    )
VAR firstrespondeddate =
    MINX (
        FILTER (
            Data,
            Data[UID] = MAX ( Data[UID] )
                && Data[ACTIVITY_TYPE] = "RESPONDED"
        ),
        Data[ACTIVITY_DATE]
    )
RETURN
    VALUE ( firstrespondeddate - firstrequesteddate )

[timcorke]

My apologies for the delay in acknowledging and best wishes to both @BarnabasToth and @Jihwan_Kim for their generosity responding. 

[BarnabasToth · Accepted solution]

If you mean to create a calculated column, here is a similar apporach:

DaysToFirstResponse = 
VAR FirstRequestDate = 
    CALCULATE (
        MIN ( 'Table'[ACTIVITY_DATE] ),
        ALLEXCEPT ( 'Table', 'Table'[UID] ),
        'Table'[ACTIVITY_TYPE] = "REQUESTED"
    )
VAR FirstResponseDate = 
    CALCULATE (
        MIN ( 'Table'[ACTIVITY_DATE] ),
        ALLEXCEPT ( 'Table', 'Table'[UID] ),
        'Table'[ACTIVITY_TYPE] = "RESPONDED"
    )
RETURN
    DATEDIFF ( FirstRequestDate, FirstResponseDate, DAY )

 
Sample file available here.
Please mark this as a solution if it resolved your issue.

Regards

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Expected result: =
VAR firstrequesteddate =
    MINX (
        FILTER (
            Data,
            Data[UID] = MAX ( Data[UID] )
                && Data[ACTIVITY_TYPE] = "REQUESTED"
        ),
        Data[ACTIVITY_DATE]
    )
VAR firstrespondeddate =
    MINX (
        FILTER (
            Data,
            Data[UID] = MAX ( Data[UID] )
                && Data[ACTIVITY_TYPE] = "RESPONDED"
        ),
        Data[ACTIVITY_DATE]
    )
RETURN
    VALUE ( firstrespondeddate - firstrequesteddate )

[timcorke]

Many thanks for your time responding, this is really helpful thank you.

My only additional query is if there is no REQUESTED or RESPONDED. Looking deeper n the data identifies that many records, likely historical, have just CLOSED or CANCELLED so return 44124 or similar negative value as there are no dates to use. 

[BarnabasToth]

For me it returns BLANK ( ) when REQUESTED or RESPONDED is missing.