Variance Calc multiple Criterias

[zeke101]

Hey Guys,

I need some help in doing a time diff calc (in seconds) based on multiple criteria. Here's the list of criteras (screenshot from excel helps explain)....

Criterias:

- If Username <> previous row User name, use calc A

- If Day# in CompletedTime <> Day# in Prev_CompletedTime, use Calc A

- If Completedime - Prev_CompletedTimeis > 2 minutes (or 120 seconds), use Calc A

-Else use Calc B

Calc A= Decision StartTime - CompletedTime

Calc B = Completedime - Prev_CompletedTime

[v-rzhou-msft · Accepted solution]

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-nP2hGoCV3...

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. 

[zeke101]

@v-rzhou-msft 

One of the calcs was backwards (my fault), but it was an easy fix and your solution worked perfectly!

Thank You!