Dynamic Date Difference

[Taro_Gulat]

Hi all, 

I need to calculate the sum of timedifference in below scenario:

I need to calculate the sum of difference between start date & end date of step = A (take minimum start date for each type) and Step = B (take minimum end date for each type). for example: difference between start date & end date of row 1 and 2, and difference between start date & end date of row 4 and 6. Record inside step are static (always A, B) but type can be more. In this case it will be 01:10:00

I am having difficulties due to blank values in start date and end date. Not able to pick the correct record. 

can anyone give some suggestion?

Thanks

[AMeyersen · Accepted solution]

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 ) )

[Anonymous · Accepted solution]

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.

[AMeyersen · Accepted solution]

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 ) )