Forum Discussion

Taro_Gulat's avatar
Taro_Gulat
Regular Visitor
1 year ago
Solved

Dynamic Date Difference

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

Need Help
  • AMeyersen's avatar
    AMeyersen
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • AMeyersen's avatar
    AMeyersen
    Resolver III
    1 year ago

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.