How to calculate duration from previous row per ID

[Anonymous]

Hi Team,

Newbie here,

I am using below measure to calculate the duration of each row based on the previous row. However, I can't seem to tweak the measure to be able to calculate duration per ID.

MEASURE:

Duration =
VAR _CurrentTaskTime =
    CALCULATE ( SELECTEDVALUE ( 'Table'[DATE/TIME] ) )
VAR _PreviousTaskTime =
    CALCULATE (
        MAX ( 'Table'[DATE/TIME] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[DATE/TIME] < _CurrentTaskTime
    )
VAR _timeDifference_IN_Minutes =
    IF (
        ISBLANK ( _PreviousTaskTime ),
        0,
        DATEDIFF ( _PreviousTaskTime, _CurrentTaskTime, MINUTE )
    )
RETURN
    _timeDifference_IN_Minutes

RESULT:

In above screenshot, notice that the ID is different but the measure still counts from the last stamp which is a different ID. It should be when  another user ID stamps another task, the duration should be back to 00:00:00 or the timer should start starting from the previous stamp per userID.

It should be like this:

Thank you guys for answering ! 🙂 

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may change your formula like DAX below.

Duration =

VAR _PreviousTaskTime =

    CALCULATE (

        MAX ( 'Table'[DATE/TIME] ),

        FILTER (

            ALLSELECTED ( 'Table' ),

            'Table'[ID] = MAX ( 'Table'[ID] )

                && 'Table'[DATE/TIME] <MAX( 'Table'[DATE/TIME])

        )

    )

RETURN

    _timeDifference_IN_Minutes

    = IF (

        ISBLANK ( _PreviousTaskTime ),

        0,

        CALCULATE (

            DATEDIFF ( _PreviousTaskTime, MAX ( 'Table'[DATE/TIME] ), MINUTE ),

            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) )

        )

    )

Best Regards,

Amy 

Community Support Team _ Amy

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

[Greg_Deckler]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...