Calculate the difference between two date time columns based on a status column

[Kym_EVO]

Hi all,

I have a data set with two date time columns based on when an enquiry was created and when the status was changed (can go through the same status multiple times).

I'm trying to determine the time (in hours), between when the enquiry was created (EnquiryCreatedDate), and when the status FIRST became "In Progress" (the status change date/time is the Enquiry Date column).

I've found this solution which I've tried to implement, however the calculated column is showing 00:00 rather than the difference between the 2 date/times. (https://community.powerbi.com/t5/Desktop/Calculate-difference-between-two-DateTime-values-in-differe...)

Here is a sample of the data I'm using:

Guardian	Status	Enquiry Date	Enquiry CreatedDate	EnquiryId
Guardian 1	Active	8/31/2022 4:17:21 AM	8/31/2022 4:17:21 AM	2331b8dc-d136-4b3c-bc9c-0f8a12c5c077
Guardian 1	In Progress	9/5/2022 3:15:28 AM	8/31/2022 4:17:21 AM	2331b8dc-d136-4b3c-bc9c-0f8a12c5c077
Guardian 2	Active	8/16/2022 3:54:07 AM	8/16/2022 3:54:07 AM	58fc0a11-c3a6-40ed-babd-02af6d4f21a1
Guardian 2	In Progress	8/19/2022 12:57:19 AM	8/16/2022 3:54:07 AM	58fc0a11-c3a6-40ed-babd-02af6d4f21a1
Guardian 3	Active	6/22/2022 2:19:39 AM	6/22/2022 2:19:39 AM	082330ea-c3c3-498e-bbdd-0fea6ddb1698
Guardian 3	Waitlisted	6/24/2022 3:46:20 AM	6/22/2022 2:19:39 AM	082330ea-c3c3-498e-bbdd-0fea6ddb1698
Guardian 3	Archived	7/31/2022 12:55:05 PM	6/22/2022 2:19:39 AM	082330ea-c3c3-498e-bbdd-0fea6ddb1698
Guardian 4	Active	6/10/2022 6:13:57 PM	6/10/2022 6:13:57 PM	393f63bf-a818-41ef-994f-44e561946a32
Guardian 4	Active	6/10/2022 6:13:57 PM	6/10/2022 6:13:57 PM	393f63bf-a818-41ef-994f-44e561946a32
Guardian 5	Active	11/8/2021 2:11:27 AM	11/8/2021 2:11:27 AM	921199c7-abc2-40e8-964b-15081e378711
Guardian 5	In Progress	11/25/2021 4:05:25 AM	11/8/2021 2:11:27 AM	921199c7-abc2-40e8-964b-15081e378711
Guardian 5	Waitlisted	12/3/2021 4:48:04 AM	11/8/2021 2:11:27 AM	921199c7-abc2-40e8-964b-15081e378711
Guardian 5	Enrolled	12/5/2021 10:07:22 AM	11/8/2021 2:11:27 AM	921199c7-abc2-40e8-964b-15081e378711
Guardian 5	Active	11/8/2021 2:11:27 AM	11/8/2021 2:11:27 AM	921199c7-abc2-40e8-964b-15081e378711
Guardian 5	In Progress	11/25/2021 4:05:25 AM	11/8/2021 2:11:27 AM	921199c7-abc2-40e8-964b-15081e378711
Guardian 5	Waitlisted	12/3/2021 4:48:04 AM	11/8/2021 2:11:27 AM	921199c7-abc2-40e8-964b-15081e378711
Guardian 5	Enrolled	12/5/2021 10:07:22 AM	11/8/2021 2:11:27 AM	921199c7-abc2-40e8-964b-15081e378711

And here is the formula I'm using to get the current calculated column that is showing 00:00

Time to Contact = 

VAR FirstContact =
    MINX(
        FILTER(
            'SPM Enquiry Report',
            'SPM Enquiry Report'[EnquiryId] = EARLIER('SPM Enquiry Report'[EnquiryId]) &&
            EARLIER('SPM Enquiry Report'[Status]) = "In Progress"
        ),
        'SPM Enquiry Report'[Enquiry Date])

RETURN IF(FirstContact<>BLANK(),FORMAT('SPM Enquiry Report'[Enquiry CreatedDate] - FirstContact,"HH:MM") )

Any help to fix the problem would be greatly appreciated. Thanks.

[DataInsights]

@Kym_EVO,

Try this calculated column:

Time to Contact = 
VAR vEnquiryCreatedDate = 'SPM Enquiry Report'[Enquiry CreatedDate]
VAR vInProgressDate =
    CALCULATE (
        MIN ( 'SPM Enquiry Report'[Enquiry Date] ),
        ALLEXCEPT ( 'SPM Enquiry Report', 'SPM Enquiry Report'[EnquiryId] ),
        'SPM Enquiry Report'[Status] = "In Progress"
    )
VAR vResult =
    DATEDIFF ( vEnquiryCreatedDate, vInProgressDate, HOUR )
RETURN
    vResult

The link below explains how to convert time units to specific time formats:

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 

[m_alireza · Accepted solution]

@Kym_EVO ,

Use this calculated column if you want to calculate the Hours, Minutes and Seconds (in HH:MM:SS format) between the Enquiry Created Date and the Enquiry Date fields for when the earliest instance of  Status = In progress for each Guardian:

Time to Contact = 

VAR _Seconds =
    DATEDIFF (
        'SPM Enquiry Report'[Enquiry CreatedDate],
        'SPM Enquiry Report'[Enquiry Date],
        SECOND
    )
VAR _Hours =
    INT ( _Seconds / 3600 )
VAR _Remainder = ( _Seconds - _Hours * 3600 ) / 3600 / 24
VAR _Filter =
    FILTER (
        'SPM Enquiry Report',
        'SPM Enquiry Report'[Guardian] = EARLIER ( 'SPM Enquiry Report'[Guardian] )
            && 'SPM Enquiry Report'[Status] = "In Progress"
    )
RETURN
    IF (
        AND (
            CALCULATE ( MIN ( 'SPM Enquiry Report'[Enquiry Date] ), _Filter ) = 'SPM Enquiry Report'[Enquiry Date],
            'SPM Enquiry Report'[Status] = "In Progress"
        ),
        FORMAT ( _Hours, "00:" ) & FORMAT ( _Remainder, "nn:ss" )
    )

 Use this calculated column if you just want to see the hours b/w the two dates

Time to Contact Hours Only =
VAR _Seconds =
    DATEDIFF (
        'SPM Enquiry Report'[Enquiry CreatedDate],
        'SPM Enquiry Report'[Enquiry Date],
        SECOND
    )
VAR _Hours =
    INT ( _Seconds / 3600 )
VAR _Remainder = ( _Seconds - _Hours * 3600 ) / 3600 / 24
VAR _Filter =
    FILTER (
        'SPM Enquiry Report',
        'SPM Enquiry Report'[Guardian] = EARLIER ( 'SPM Enquiry Report'[Guardian] )
            && 'SPM Enquiry Report'[Status] = "In Progress"
    )
RETURN
    IF (
        AND (
            CALCULATE ( MIN ( 'SPM Enquiry Report'[Enquiry Date] ), _Filter ) = 'SPM Enquiry Report'[Enquiry Date],
            'SPM Enquiry Report'[Status] = "In Progress"
        ),
        _Hours
    )

Example Output: 

[Kym_EVO]

Perfect, that's exactly what I was trying to do, thanks @m_alireza!