Variance Calc multiple Criterias

Hi zeke101 

I use the data in your Screenshot to have a test.

Firstly I add an index column into the Table.

Then we build a calculated column to achieve your goal.

Clean Time =

VAR _Calc_A =

    TIMEVALUE('Table'[DECISION_STARTTIME]) - TIMEVALUE('Table'[COMPLETEDTIME])

VAR _Calc_B =

    TIMEVALUE('Table'[COMPLETEDTIME]) - TIMEVALUE('Table'[PREV_COMPLETEDTIME])

VAR _PreviousUserName =

    IF (

        'Table'[Index] = 1,

        'Table'[USERBANE],

        CALCULATE (

            MAX ( 'Table'[USERBANE] ),

            FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )

        )

    )

RETURN

    IF (

        'Table'[USERBANE] <> _PreviousUserName,

        _Calc_A,

        IF (

            DAY ( 'Table'[COMPLETEDTIME] ) <> DAY ( 'Table'[PREV_COMPLETEDTIME] ),

            _Calc_A,

            IF (

                DATEDIFF ( 'Table'[PREV_COMPLETEDTIME], 'Table'[COMPLETEDTIME], SECOND ) > 120,

                _Calc_A,

                _Calc_B

            )

        )

)

We need to change the column type as Time and Format as h:nn:ss in column tools.

Result is as below:

Due to I don’t know how do you deal with the first user, so when I make the first username = himself, so the first user use CalcB.

And the others’ calculation are the same as your Calculation logic.

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/Ea8rb6-nP2hGoCV3fGlnGb0BNeLSi-VYTWfiFamBrWvSAA?e=DIOmLB

Best Regards,

Rico Zhou

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