DAX or M Query to subtract dates on different rows

[rax99]

Ok so bare with me this is going to get a little confusing;

I want to get the values in the desired column like the table below:

The calculation is: [StartTime] minus the previous row [Endtime]. So row 2 [Starttime] minus row 1 [Endtime] would be 17:04:48 minus 17:04:31 (=17sec). However I want to exclude rows where CustAgentFl = 0 AND Transferflag = 0 before doing the date subtract calculation. Also, if Starttime- Endtime is <0 then just 0.

The rows are all grouped by the same NID, so of course the DAX or M query will need to group by the NID.

Any help would be great.Thanks

[v-piga-msft · Accepted solution]

Hi @rax99,

For your requirement, you could create a calculated column with the formula below. Please note, you need to create the index column in Query Editor firstly.

Column = 
VAR a =
    CALCULATE (
        MAX ( 'Table1'[EndTime] ),
        FILTER ( ALLEXCEPT('Table1',Table1[NID]), 'Table1'[Index] <= EARLIER ( 'Table1'[Index] ) - 1 )
    )
RETURN
    IF (
        'Table1'[CustAgentFI] = 0
            && 'Table1'[TransferFIag] = 0,
        TIME ( HOUR ( 0 ), MINUTE ( 0 ), SECOND ( 0 ) ),
        IF (
            ISBLANK ( a ),
            TIME ( HOUR ( 0 ), MINUTE ( 0 ), SECOND ( 0 ) ),
            IF (
                'Table1'[StartTime] - a
                    < 0,
                TIME ( HOUR ( 0 ), MINUTE ( 0 ), SECOND ( 0 ) ),
                'Table1'[StartTime] - a
            )
        )
    )

Here is my test result.

More details, you could refer to the attachment.

Best  Regards,

Cherry