Dynamic Date Difference

If you are looking for a DAX measure, you could use a formula like this:

Duration in Minutes =
VAR _step_type =
    SUMMARIZECOLUMNS (
        'Table'[Type],
        "StartA",
            CALCULATE (
                MIN ( 'Table'[Start] ),
                'Table'[Step] = "A",
                ALLEXCEPT ( 'Table', 'Table'[Type] )
            ),
        "EndB",
            CALCULATE (
                MIN ( 'Table'[End] ),
                'Table'[Step] = "B",
                ALLEXCEPT ( 'Table', 'Table'[Type] )
            )
    )
RETURN
    SUMX ( _step_type, DATEDIFF ( [StartA], [EndB], MINUTE ) )

Hi Taro_Gulat ,

I create a table as you mentioned.

Then I think you can create a calculated column and here is the DAX code.

Column = 
VAR _MinStartDate =
    CALCULATE (
        MIN ( 'Table'[Start Date] ),
        FILTER (
            'Table',
            'Table'[Step] = "A"
                && 'Table'[Type] = EARLIER ( 'Table'[Type] )
        )
    )
VAR __MinEndDate =
    CALCULATE (
        MIN ( 'Table'[End Date] ),
        FILTER (
            'Table',
            'Table'[Step] = "B"
                && 'Table'[Type] = EARLIER ( 'Table'[Type] )
        )
    )
RETURN
    DATEDIFF ( _MinStartDate, __MinEndDate, MINUTE )

If you want to change the Column's format, you can do this step.

Column 2 = 
VAR _TotalTime =
    SUMX (
        SUMMARIZE ( 'Table', 'Table'[Type], "UniqueColumn", MAX ( 'Table'[Column] ) ),
        [UniqueColumn]
    )
RETURN
    FORMAT ( INT ( _TotalTime / 60 ), "00" ) & ":"
        & FORMAT ( MOD ( _TotalTime, 60 ), "00" ) & ":"
        & FORMAT ( 0, "00" )

Best Regards

Yilong Zhou

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