Forum Discussion

Kym_EVO's avatar
Kym_EVO
Frequent Visitor
3 years ago
Solved

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

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...
Need Help
  • m_alireza's avatar
    m_alireza
    3 years ago

    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: 

     



DataInsights's avatar
DataInsights
Super User
3 years ago

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