DAX expression for subtracting value from next or prior row from value in current row

[vissvess]

Dear Community experts,

Its been a long search for a proper expression to get a calculated column or measure to get a duration of activity in the following table. A small help would be much appreciated.

My data is as follows

Date        Creator   Routing  Time    Duration

1/6/2019 Adam     FE           8:20:21

1/6/2019 Adam     FE           8:21:22

1/6/2019 Adam     FE           8:22:05

1/6/2019 Eve         FE           8:20:21

and so on..

I need the duration to be filled with the time difference between current and previous time.

For instance, row 1 duration to be null. Row 2 should have 0:01:01 or 61 sec. This also has to check for the same creator for the calculation.

For this, M-script approch in query was tried out. First to have a index columns starting with 0 & 1 and merging with itself to bring the earlier value to current row. The method is so time consuming and the script hangs and crashes as the data is so long with nearly 1M lines.

Thanks

[Anonymous · Accepted solution]

Here's what it looks like:

and here are two equivalent expressions. You have to assess which one will be faster.

1. Duration = 
   var __currentCreator = RoutingTable[Creator]
   var __currentRouting = RoutingTable[Routing]
   var __currentDate = RoutingTable[Date]
   var __currentTime = RoutingTable[Time]
   var __previousTime =
       MAXX(
           FILTER(
               RoutingTable,
               RoutingTable[Creator] = __currentCreator
               && RoutingTable[Date] = __currentDate
               && RoutingTable[Routing] = __currentRouting
               && RoutingTable[Time] < __currentTime
           ),
           RoutingTable[Time]
       )
   var __timeDiff = DATEDIFF(__previousTime, __currentTime, SECOND)
   return
       __timeDiff

2. Duration 2 = 
   var __currentTime = RoutingTable[Time]
   var __prevTime =
       CALCULATE(
           MAX( RoutingTable[Time] ),
           RoutingTable[Time] < __currentTime,
           ALLEXCEPT(
               RoutingTable,
               RoutingTable[Creator],
               RoutingTable[Date],
               RoutingTable[Routing]
           )
       )
   var __timeDiff = DATEDIFF( __prevTime, __currentTime, SECOND )
   return
       __timeDiff

Hope it'll help.

Actually, I've checked which one is faster on a 1-million row data set. Forget about the second option which uses CALCULATE (and context transition) - this calculation never ends. The first calculation, which uses FILTER, returns after... several seconds. So now you have a solution that will take seconds to calculate on a 2-million row dataset.

Best

Darek

[vissvess]

@Anonymous  Thanks a lot........

Very much excited as the results genterated.

Kudos....

[vissvess]

Further The resultant value to be checked that it is below say 240, above that to be replaced with null.

I was trying to append with IF condition. That throws "Expressions that yield variant data-type cannot be used to define calculated columns." error.

On trying with seperate custom column based on generated duration column with same if condition results the same.

Any help would be thankful.

[Zubair_Muhammad]

@vissvess 

Try this calculated column

Column =
DATEDIFF (
    MAXX (
        TOPN (
            1,
            FILTER (
                Table1,
                [Creator] = EARLIER ( [Creator] )
                    && [Duration] < EARLIER ( [Duration] )
            ),
            [Duration], DESC
        ),
        [Duration]
    ),
    [Duration],
    SECOND
)

[vissvess]

@Zubair_Muhammad , Thanks for the code and your work..

The code seems to work without error but the output is not in the format desired. The expected output type would be duration. The code provides output as a single digit which does not mean it. Any data type transformation doesnot work.

Also to note, the code was tested with a small data set after having error message stating "Memory not sufficient...." for my original one of nearly 2M rows.

Further support would be much appreciated.

Thanks